package com.scalar.chiptrack.sales.dao.quote;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.*;
import com.scalar.chiptrack.commons.dao.ScreenFieldPropertiesDAO;
import com.scalar.chiptrack.commons.bean.IPaginator;
import com.scalar.chiptrack.commons.bean.IPaginatorImpl;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.marketing.dao.MarketingDAO;
import com.scalar.chiptrack.sales.*;
import com.scalar.chiptrack.sales.forms.quote.QuotationForm;
import com.scalar.chiptrack.sales.dao.*;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.alarms.Alarm;
import com.scalar.chiptrack.alarms.AlarmUser;
import com.scalar.chiptrack.alarms.Comment;
import com.scalar.chiptrack.alarms.dao.AlarmDAO;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.users.Functionality;
import com.scalar.chiptrack.users.dao.UserDAO;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.text.Format;
import java.io.File;

import org.apache.struts.util.LabelValueBean;
import org.apache.struts.util.MessageResources;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

/**
 * User: Basha
 * Date: Feb 4, 2004
 * Time: 2:21:01 AM
 */

public class QuotationDAO implements Constants
{

    public void Quotation ()
    {
    }

    public static Quotation getQuotationCustomerDetails ( String customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Quotation quotation = null;
        int customerIdFromDB = -1;
        int shippingAddressIdFromDB = 0;
        int billingAddressIdFromDB = 0;

        try
        {
            conn = DataSource.getConnection ();
            pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.selectCustomer" ) );

            pstmt.setInt ( 1, Integer.parseInt ( customerId ) );

            rs = pstmt.executeQuery ();
            if ( rs.next () == false )
            {
                //Customer for SalesOrder not found, ???Throw exception
            }
            else
            {
                quotation = new Quotation ();

                customerIdFromDB = rs.getInt ( "Customer_ID" );
                quotation.setCustomerId ( customerIdFromDB );

                quotation.setCustomerName ( rs.getString ( "C_Name" ) );
                quotation.setContactPerson ( rs.getString ( "C_Contact_Person" ) );
                String shippingMethod1 = rs.getString ( "C_Shipping_Method_1" );
                String shippingMethod2 = rs.getString ( "C_Shipping_Method_2" );
                String shippingMethod3 = rs.getString ( "C_Shipping_Method_3" );
                String shippingAccount1 = rs.getString ( "C_Act_Number_1" );
                String shippingAccount2 = rs.getString ( "C_Act_Number_2" );
                String shippingAccount3 = rs.getString ( "C_Act_Number_3" );
                quotation.setRegionalRep ( rs.getString ( "C_Rep_Office" ) );
                quotation.setSalesPerson ( rs.getString ( "C_Sales_Person" ) );
                quotation.setShippingInstructions ( rs.getString ( "C_Shipping_Instructions" ) );

                shippingAddressIdFromDB = rs.getInt ( "C_Default_Shipping_Address_Id" );
                billingAddressIdFromDB  = rs.getInt ( "C_Default_Billing_Address_Id" );
                quotation.setSalesRep( rs.getString ( "C_Sales_Rep" ) );
                quotation.setShippingInstructionsId( rs.getInt("C_SHIPPING_INSTRUCTIONS_ID"));
                quotation.setDistributor( rs.getBoolean( "C_Is_Distributor" ) );
                quotation.setDeliveryTerms( rs.getString ( "DELIVERY_TERMS" ) );
                //quotation.setShippingTerms( rs.getString ( "C_PAY_TERMS" ) );
                quotation.setShippingAddressId ( shippingAddressIdFromDB );
                quotation.setBillingAddressId ( billingAddressIdFromDB );

                quotation.setShippingMethodName ( shippingMethod1 );
                quotation.setShippingAccount ( shippingAccount1 );
                if ( rs != null )
                {
                    rs.close();
                    rs = null;
                }
                if (pstmt != null)
                {
                    pstmt.close();
                    pstmt = null;
                }
                if( conn != null )
                {
                    conn.close();
                    conn = null;
                }

                Address address = CustomerDAO.getBillingAddress( quotation.getBillingAddressId () );//CustomerDAO.getAddress ( quotation.getBillingAddressId () );
                if ( address != null )
                {
                    quotation.setBillingContactPerson( address.getContactPerson() );
                    quotation.setBillingAddressAsObject ( address );
                    quotation.setBillingAddress ( address.getAddressAsString () );
                }

                Address shipAddress = CustomerDAO.getAddressWithContactPersonDetails( quotation.getShippingAddressId () ); //CustomerDAO.getAddress ( quotation.getShippingAddressId () );
                if ( shipAddress != null )
                {
                    quotation.setShippingContactPerson(shipAddress.getContactPerson());
                    quotation.setShippingAddressAsObject ( shipAddress );
                    quotation.setShippingAddress ( shipAddress.getAddressAsString());
                }

                ArrayList shippingList = new ArrayList ();
                NewSalesOrderDAO.loadCustomerAddressesOfType(shippingList, quotation.getCustomerId (), "Shipping");
                quotation.setShippingAddresses ( shippingList );

                ArrayList shippingMethods = null;

                shippingMethods = CustomerDAO.loadSalesOrderShippingMethods ( customerIdFromDB );
                quotation.setShippingMethods ( shippingMethods );
                String contactPersonName = quotation.getContactPerson();
                ArrayList contactPersons = CustomerDAO.getAllContactPersonsForCustomer( customerIdFromDB );
                contactPersons.add(quotation.getCustomerName());
                quotation.setContactPersons(contactPersons);
                quotation.setAllShippingTerms(SalesDAO.getAllShippingTerms());
                quotation.setAllDeliveryTerms(SalesDAO.getAllDeliveryTerms( null ));
                quotation.setContactPersonsForBillingAddress(CustomerDAO.getAllContactPersonsForAddress(  billingAddressIdFromDB));
                quotation.setContactPersonsForShippingAddress(CustomerDAO.getAllContactPersonsForAddress( shippingAddressIdFromDB));

                if(contactPersonName != null &&  contactPersonName.trim().length()!= 0)
                {
                    ContactPerson contactPerson = CustomerDAO.getContactPersonDetails (customerIdFromDB , quotation.getContactPerson());
                    if(contactPerson != null)
                    {
                        quotation.setContactPerson( contactPerson.getContactPerson() );
                        quotation.setPhoneNumber( contactPerson.getPhoneNumber() );
                        quotation.setCellNumber( contactPerson.getCellNumber() );
                        quotation.setFaxNumber( contactPerson.getFaxNumber() );
                        quotation.setEmail( contactPerson.getEmail() );
                    }
                    else if(contactPerson == null)
                    {
                        quotation.setContactPerson( "" );
                        quotation.setPhoneNumber( "" );
                        quotation.setCellNumber( "" );
                        quotation.setFaxNumber( "" );
                        quotation.setEmail( "" );
                    }
                }
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Sales Order Info from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return quotation;
    }
    public static void getQuotationCustomerDetail ( QuotationForm quotation, String customerId ) throws ScalarException
        {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            int customerIdFromDB = -1;
            int shippingAddressIdFromDB = 0;
            int billingAddressIdFromDB = 0;

            try
            {
                conn = DataSource.getConnection ();
                pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "sales.customer.selectCustomer" ) );

                pstmt.setInt ( 1, Integer.parseInt ( customerId ) );

                rs = pstmt.executeQuery ();
                if ( rs.next () == false )
                {
                    //Customer for SalesOrder not found, ???Throw exception
                }
                else
                {
                    customerIdFromDB = rs.getInt ( "Customer_ID" );
                    quotation.setCustomerId ( customerIdFromDB );
                    quotation.setCustomerName ( rs.getString ( "C_Name" ) );
                    quotation.setContactPerson ( rs.getString ( "C_Contact_Person" ) );
                    String shippingMethod1 = rs.getString ( "C_Shipping_Method_1" );
                    String shippingMethod2 = rs.getString ( "C_Shipping_Method_2" );
                    String shippingMethod3 = rs.getString ( "C_Shipping_Method_3" );
                    String shippingAccount1 = rs.getString ( "C_Act_Number_1" );
                    String shippingAccount2 = rs.getString ( "C_Act_Number_2" );
                    String shippingAccount3 = rs.getString ( "C_Act_Number_3" );
                    quotation.setRegionalRep ( rs.getString ( "C_Rep_Office" ) );
                    quotation.setSalesPerson ( rs.getString ( "C_Sales_Person" ) );
                    quotation.setShippingInstructions ( rs.getString ( "C_Shipping_Instructions" ) );

                    shippingAddressIdFromDB = rs.getInt ( "C_Default_Shipping_Address_Id" );
                    billingAddressIdFromDB  = rs.getInt ( "C_Default_Billing_Address_Id" );
                    quotation.setSalesRep( rs.getString ( "C_Sales_Rep" ) );
                    quotation.setShippingInstructionsId( rs.getInt("C_SHIPPING_INSTRUCTIONS_ID"));
                    quotation.setDistributor( rs.getBoolean( "C_Is_Distributor" ) );
                    quotation.setDeliveryTerms( rs.getString ( "DELIVERY_TERMS" ) );
                    //quotation.setShippingTerms( rs.getString ( "C_PAY_TERMS" ) );
                    quotation.setShippingAddressId ( shippingAddressIdFromDB );
                    quotation.setBillingAddressId ( billingAddressIdFromDB );

                    quotation.setShippingMethodName ( shippingMethod1 );
                    quotation.setShippingAccount ( shippingAccount1 );
                    if ( rs != null )
                    {
                        rs.close();
                        rs = null;
                    }
                    if (pstmt != null)
                    {
                        pstmt.close();
                        pstmt = null;
                    }
                    if( conn != null )
                    {
                        conn.close();
                        conn = null;
                    }

                    Address address = CustomerDAO.getBillingAddress( quotation.getBillingAddressId () );//CustomerDAO.getAddress ( quotation.getBillingAddressId () );
                    if ( address != null )
                    {
                        quotation.setBillingContactPerson( address.getContactPerson() );
                        //quotation.setBillingAddressAsObject ( address );
                        quotation.setBillingAddress ( address.getAddressAsString () );
                    }

                    Address shipAddress = CustomerDAO.getAddressWithContactPersonDetails( quotation.getShippingAddressId () ); //CustomerDAO.getAddress ( quotation.getShippingAddressId () );
                    if ( shipAddress != null )
                    {
                        quotation.setShippingContactPerson(shipAddress.getContactPerson());
                        //quotation.setShippingAddressAsObject ( shipAddress );
                        quotation.setShippingAddress ( shipAddress.getAddressAsString());
                    }

                    ArrayList shippingList = new ArrayList ();
                    NewSalesOrderDAO.loadCustomerAddressesOfType(shippingList, quotation.getCustomerId (), "Shipping");
                    quotation.setShippingAddresses ( shippingList );

                    ArrayList shippingMethods = null;

                    shippingMethods = CustomerDAO.loadShippingMethodsForCustomerAndOdm( quotation.getCustomerId(), quotation.getOdmId() );//CustomerDAO.loadSalesOrderShippingMethods ( customerIdFromDB );
                    quotation.setShippingMethods ( shippingMethods );
                    String contactPersonName = quotation.getContactPerson();
                    ArrayList contactPersons = CustomerDAO.getAllContactPersonsForCustomer( customerIdFromDB );
                    contactPersons.add(quotation.getCustomerName());
                    quotation.setContactPersons(contactPersons);

                    quotation.setAllDeliveryTerms(SalesDAO.getAllDeliveryTerms( null ));
                    quotation.setContactPersonsForBillingAddress(CustomerDAO.getAllContactPersonsForAddress(  billingAddressIdFromDB));
                    quotation.setContactPersonsForShippingAddress(CustomerDAO.getAllContactPersonsForAddress( shippingAddressIdFromDB));

                    if(contactPersonName != null &&  contactPersonName.trim().length()!= 0)
                    {
                        ContactPerson contactPerson = CustomerDAO.getContactPersonDetails (customerIdFromDB , quotation.getContactPerson());
                        if(contactPerson != null)
                        {
                            quotation.setContactPerson( contactPerson.getContactPerson() );
                            quotation.setPhoneNumber( contactPerson.getPhoneNumber() );
                            quotation.setCellNumber( contactPerson.getCellNumber() );
                            quotation.setFaxNumber( contactPerson.getFaxNumber() );
                            quotation.setEmail( contactPerson.getEmail() );
                        }
                        else if(contactPerson == null)
                        {
                            quotation.setContactPerson( "" );
                            quotation.setPhoneNumber( "" );
                            quotation.setCellNumber( "" );
                            quotation.setFaxNumber( "" );
                            quotation.setEmail( "" );
                        }
                    }
                }
            }
            catch ( Exception sqlex )
            {
                sqlex.printStackTrace ();
                throw new ScalarException ( "Could not get Sales Order Info from database", sqlex );
            }
            finally
            {
                if ( rs != null )
                {
                    try
                    {
                        rs.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( pstmt != null )
                {
                    try
                    {
                        pstmt.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( conn != null )
                {
                    try
                    {
                        conn.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
            }
        }


    public static Quotation loadQuotation ( String salesOrderId, boolean isTransfer ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        Quotation quotation = null;
        String salesOrderNo = null;
        boolean isApproved  = false;


        if ( salesOrderId == null )
            return null;

        try
        {
            conn = DataSource.getConnection ();

            String queryString = "SELECT Quotation_ID, Quotation_No, Customer_ID, Q_Customer_PO_Number, Q_Contact_Person, Q_Sales_Person, Q_Shipping_Method, Q_Shipping_Account, Q_Billing_Address_ID, Q_Shipping_Address_ID, Q_Comments, Q_Shipping_Instructions, Q_EndUser, Q_Status, Q_Booking_Date, Q_Is_Sample, Q_Is_Cancel, Q_Is_Pending, Q_Is_Shipped, Q_Entered_By_ID, Q_Entered_By_Name, Q_Sales_Tax_Type, Q_Sub_Total, Q_Sales_Tax, Q_Frieght_Tax, Q_Misc, Alarm_ID, Timestamp, Regional_Rep, Shipping_Terms, Delivery_Terms, Q_Misc_Name, Q_Show_Pro_Forma_Invoice, Expiry_Date, Q_Billing_Contact_Person, Q_Shipping_Contact_Person, Q_Sales_Rep, Q_Revision, Q_PO_Rev, Q_PO_Date, Q_ODM_ID , Q_Is_Customer_Ship_Address, datediff( day, current_timestamp, Expiry_Date ) as Is_Expired, f.Freight_Instructions, Q_SHIPPING_INSTRUCTIONS_ID, Q_Freight_Instructions_Id, Q_ENDCUSTOMER_ID, Q_QUOTE_TYPE, Q_APPLICATION, Q_ADD_NEW_APPLICATION, Q_NEW_END_USER, Q_NEW_END_CUSTOMER, Q_EXPECTED_PO_DATE, Q_PROJECT_NAME, Q_OPPORTUNITY_ID FROM Quotation_Table LEFT OUTER JOIN Freight_Instructions f ON f.FI_ID = Q_Freight_Instructions_Id WHERE Quotation_ID = ?";
            if ( isTransfer )
            {
                queryString = "SELECT Quotation_ID, Quotation_No, Customer_ID, Q_Customer_PO_Number, Q_Contact_Person, Q_Sales_Person, Q_Shipping_Method, Q_Shipping_Account, Q_Billing_Address_ID, Q_Shipping_Address_ID, Q_Comments, Q_Shipping_Instructions, Q_EndUser, Q_Status, CURRENT_TIMESTAMP AS Q_Booking_Date, Q_Is_Sample, Q_Is_Cancel, Q_Is_Pending, Q_Is_Shipped, Q_Entered_By_ID, Q_Entered_By_Name, Q_Sales_Tax_Type, Q_Sub_Total, Q_Sales_Tax, Q_Frieght_Tax, Q_Misc, Alarm_ID, Timestamp, Regional_Rep, Shipping_Terms, Delivery_Terms, Q_Misc_Name, Q_Show_Pro_Forma_Invoice, Expiry_Date, Q_Billing_Contact_Person, Q_Shipping_Contact_Person, Q_Sales_Rep, Q_Revision, Q_PO_Rev, Q_PO_Date, Q_ODM_ID , Q_Is_Customer_Ship_Address, datediff( day, current_timestamp, Expiry_Date ) as Is_Expired, f.Freight_Instructions, Q_SHIPPING_INSTRUCTIONS_ID, Q_Freight_Instructions_Id, Q_ENDCUSTOMER_ID, Q_QUOTE_TYPE, Q_APPLICATION, Q_ADD_NEW_APPLICATION, Q_NEW_END_USER, Q_NEW_END_CUSTOMER, Q_EXPECTED_PO_DATE, Q_PROJECT_NAME, Q_OPPORTUNITY_ID  FROM Quotation_Table LEFT OUTER JOIN Freight_Instructions f ON f.FI_ID = Q_Freight_Instructions_Id WHERE Quotation_ID = ?";
            }

            pstmt = conn.prepareStatement ( queryString );
            pstmt.setInt ( 1, Integer.parseInt ( salesOrderId ) );

            rs = pstmt.executeQuery ();
            if ( rs.next () == false )
            {
                // TBD
            }
            else
            {
                salesOrderId = rs.getString ( "Quotation_ID" );
                salesOrderNo = rs.getString ( "Quotation_No" );
                quotation = new Quotation ( Integer.parseInt ( salesOrderId ),
                                            rs.getInt ( "Customer_ID" ),
                                            rs.getString ( "Q_Customer_PO_Number" ),
                                            rs.getString ( "Q_Contact_Person" ),
                                            rs.getString ( "Q_Sales_Person" ),
                                            rs.getString ( "Q_Shipping_Method" ),
                                            rs.getString ( "Q_Shipping_Account" ),
                                            rs.getInt ( "Q_Billing_Address_ID" ),
                                            rs.getInt ( "Q_Shipping_Address_ID" ),
                                            rs.getString ( "Q_Comments" ),
                                            rs.getString ( "Q_Shipping_Instructions" ),
                                            rs.getString ( "Q_EndUser" ),
                                            rs.getDate ( "Q_Booking_Date" ), ( rs.getInt ( "Q_Is_Sample" ) == 0 ? true : false ) );
                quotation.setQuotationNo ( salesOrderNo );
                quotation.setEnteredById ( rs.getString ( "Q_Entered_BY_ID" ) );
                quotation.setEnteredByName ( rs.getString ( "Q_Entered_BY_Name" ) );
                quotation.setSalesTaxType ( rs.getString ( "Q_Sales_Tax_type" ) );

                quotation.setSubTotalAsDouble (rs.getDouble("Q_Sub_Total"));
                quotation.setSalesTaxAsDouble (rs.getDouble("Q_Sales_Tax"));
                quotation.setFrieghtAsDouble (rs.getDouble("Q_Frieght_Tax"));
                quotation.setMiscAsDouble (rs.getDouble("Q_Misc"));
                quotation.setOrderTotalAsDouble (quotation.getSubTotalAsDouble() + quotation.getSalesTaxAsDouble() + quotation.getFrieghtAsDouble() + quotation.getMiscAsDouble());

                quotation.setEnteredDate ( StringUtils.formatDateToString ( rs.getDate ( "Timestamp" ) ) );
                quotation.setRegionalRep ( rs.getString ( "Regional_Rep" ) );
                quotation.setShippingTerms ( rs.getString ( "Shipping_Terms" ) );
                quotation.setDeliveryTerms ( rs.getString ( "Delivery_Terms" ) );
                quotation.setMiscName ( rs.getString ( "Q_Misc_Name" ) );
                quotation.setExpiryDate ( StringUtils.formatDateToString ( rs.getDate ( "Expiry_Date" ), null ) );
                quotation.setBillingContactPerson(rs.getString( "Q_Billing_Contact_Person" ));
                quotation.setShippingContactPerson(rs.getString( "Q_Shipping_Contact_Person" ));
                quotation.setSalesRep( rs.getString( "Q_Sales_Rep" ) );

                quotation.setRevision( rs.getInt("Q_Revision"));
                quotation.setPoRev( rs.getString("Q_PO_Rev"));
                quotation.setPoDate( StringUtils.formatDateToString ( rs.getDate("Q_PO_Date")));
                quotation.setOdmId( rs.getInt("Q_ODM_ID"));
                quotation.setIsEndCustomerShipAddress( rs.getInt("Q_Is_Customer_Ship_Address") == 0 ? false : true ); //IsCustomerShipAddress in DB Field is mapping to the IsEndCustomerShipAddress. if isCustShipAddrs is TRUE Then IsEndCustShipAddrs is FALSE Hence storing FALSE in the property

                quotation.setStatus( rs.getInt("Is_Expired") < 0 ? "Expired" : "Pending" );
                quotation.setOdm(CustomerDAO.getCustomerName( null, quotation.getOdmId()));
                quotation.setEndUserId ( EndUserDAO.getEndUserIdByName ( quotation.getEndUser (), quotation.getCustomerId () ) +"" ) ;

                quotation.setFreightInstructions( rs.getString ( "Freight_Instructions" ) );
                quotation.setShippingInstructionsId ( rs.getInt( "Q_SHIPPING_INSTRUCTIONS_ID") );
                quotation.setFreightInstructionsId( rs.getInt ( "Q_Freight_Instructions_Id") );
                quotation.setEndCustomerId( rs.getInt("Q_ENDCUSTOMER_ID") );
                quotation.setQuoteType( rs.getString ( "Q_QUOTE_TYPE" ) );
                quotation.setApplication( rs.getString( "Q_APPLICATION" ) );
                quotation.setAddNewApplication( rs.getString ( "Q_ADD_NEW_APPLICATION" ) );
                quotation.setNewEndUserName( rs.getString ( "Q_NEW_END_USER" ) );
                quotation.setNewEndCustomerName( rs.getString ( "Q_NEW_END_CUSTOMER" ) );
                quotation.setExpectedPoDate( StringUtils.formatDateToString ( rs.getDate("Q_EXPECTED_PO_DATE")));
                quotation.setProjectName( rs.getString("Q_PROJECT_NAME") );
                quotation.setOpportunityId( rs.getString("Q_OPPORTUNITY_ID") );
                if ( rs != null )
                {
                    try{ rs.close (); } catch ( SQLException excp ){ }
                    rs = null;
                }
                if ( pstmt != null )
                {
                    try{ pstmt.close (); } catch ( SQLException excp ){ }
                    pstmt = null;
                }
                if ( conn != null )
                {
                    try{ conn.close (); }catch ( SQLException excp ){ }
                    conn = null;
                }
                if ( quotation.getAddNewApplication() != null && quotation.getAddNewApplication().trim().length() > 0 )
                {
                    quotation.setApplication( "-2" );//to select add new app
                }
                quotation.setEndCustomer ( CustomerDAO.getCustomerName( null, quotation.getEndCustomerId() ) );
                Customer customer = CustomerDAO.loadCustomerById( quotation.getCustomerId() );
                quotation.setCustomerName( customer.getCustomerName() );
                quotation.setDistributor( customer.getIsDistributor() );
                
                    ArrayList presentList = loadOrderLineItems ( null, quotation.getQuotationId () );

                double total = 0.0f ;
                double finalSalesTax = 0.0;
                float taxvalue = 0.0f;

                taxvalue = SalesOrderDAO.getTaxValue ( null, quotation.getSalesTaxType () );
                Float ff = new Float ( taxvalue );
                double tax = ff.doubleValue ();

                for ( int i = 0; i < presentList.size (); i++ )
                {
                    LineItem templineItem = ( LineItem ) presentList.get ( i );
                    total = total + templineItem.getExtendedPriceForQuote ();
                    if ( templineItem.getIsTaxable () )
                    {
                        finalSalesTax = finalSalesTax + ( templineItem.getExtendedPriceForQuote () * ( tax / 100 ) );
                    }
                    if(templineItem.getIsApproved())
                    {
                       isApproved=true;
                    }
                }
                quotation.setApproved(isApproved);
                quotation.setSalesTaxValue ( taxvalue );
                quotation.setFinalSubTotal ( total );
                quotation.setFinalSalesTax ( finalSalesTax );
                quotation.setFinalOrderTotal ( total + finalSalesTax + quotation.getMiscAsDouble () + quotation.getFrieghtAsDouble () );


                quotation.setShippingMethodName ( quotation.getShippingMethod () );

                //Address address = CustomerDAO.getAddress ( quotation.getBillingAddressId () );
                Address address = CustomerDAO.getAddressWithContactPersonDetails( quotation.getBillingAddressId (), quotation.getBillingContactPerson(), quotation.getCustomerId() );
                StringBuffer strBufferAddress = new StringBuffer ();

                if ( address != null )
                {
                    quotation.setBillingAddressAsObject ( address );
                    quotation.setBillingAddress ( address.getAddressAsString ( ) );
                }
                int shippingAddressId = quotation.getShippingAddressId ();
                if ( shippingAddressId < 0 )
                {
                    shippingAddressId = SalesOrderDAO.getDefaultShippingAddressId( quotation.getCustomerId () );
                    quotation.setShippingAddressId ( shippingAddressId );
                    quotation.setShippingContactPerson (CustomerDAO.getAddress( shippingAddressId ).getContactPerson());
                }
                address = CustomerDAO.getAddressWithContactPersonDetails ( shippingAddressId, quotation.getShippingContactPerson() );
                if ( address != null )
                {
                    quotation.setShippingAddressAsObject ( address );
                    quotation.setShippingAddress ( address.getAddressAsString ( ) );
                }
                String clientaddressId =  UserDAO.getClientAddressId();
                String addressId = null;
                String clientname = null;

                int finalAddressId = 0;
                if(clientaddressId != null)
                {
                    StringTokenizer st = new StringTokenizer(clientaddressId,"@");
                    try
                    {
                        addressId = st.nextToken();
                        clientname = st.nextToken();
                    }catch(NoSuchElementException e)
                    {
                         clientname  = "";
                    }
                }
                if(addressId  != null)
                {
                    finalAddressId = Integer.parseInt(addressId);
                }
                Address clientAddress= UserDAO.loadClientAddressDetails( null , finalAddressId );
                quotation.setClientAddressAsObject(clientAddress);
                String clientAddress1 = SalesOrder.getClientAddressAsString(clientAddress);
                quotation.setClientAddress(clientAddress1);
                quotation.setClientName(clientname);
                String contactPersonName = quotation.getContactPerson();
                if( contactPersonName != null && contactPersonName.equals(quotation.getCustomerName()) )
                {
                    Address addressObject = getQuotationAddress (null, quotation.getQuotationId() );
                    if(addressObject != null)
                    {
                        quotation.setContactPerson(quotation.getCustomerName());
                        quotation.setEmail ( addressObject.getEmail () );
                        quotation.setPhoneNumber ( addressObject.getPhoneNumber () );
                        quotation.setFaxNumber ( addressObject.getFaxNumber () );
                        quotation.setCellNumber ("");
                   }
                }
                else if(contactPersonName != null &&  contactPersonName.trim().length()!= 0)
                {
                    ContactPerson contactPerson = null;
                    contactPerson = CustomerDAO.getContactPersonDetails (quotation.getCustomerId() , quotation.getContactPerson());
                    if(contactPerson != null)
                    {
                         quotation.setContactPerson( contactPerson.getContactPerson() );
                         quotation.setPhoneNumber( contactPerson.getPhoneNumber() );
                         quotation.setCellNumber( contactPerson.getCellNumber() );
                         quotation.setFaxNumber( contactPerson.getFaxNumber() );
                         quotation.setEmail( contactPerson.getEmail() );
                    }
                    else if(contactPerson == null)
                    {
                         contactPerson = new ContactPerson();
                         quotation.setContactPerson( "" );
                         quotation.setPhoneNumber( "" );
                         quotation.setCellNumber( "" );
                         quotation.setFaxNumber( "" );
                         quotation.setEmail( "" );
                    }
                }
                ArrayList contactPersons = CustomerDAO.getAllContactPersonsForCustomer( quotation.getCustomerId());
                contactPersons.add(quotation.getCustomerName());
                quotation.setContactPersons(contactPersons);
                quotation.setAllShippingTerms(SalesDAO.getAllShippingTerms());
                quotation.setAllDeliveryTerms(SalesDAO.getAllDeliveryTerms( null ));
                quotation.setContactPersonsForBillingAddress(CustomerDAO.getAllContactPersonsForAddress(  quotation.getBillingAddressId()));
                quotation.setContactPersonsForShippingAddress(CustomerDAO.getAllContactPersonsForAddress( quotation.getShippingAddressId()));
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Quotation Info from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return quotation;
    }

    public static void loadQuotation ( int salesOrderId, boolean isTransfer, QuotationForm quotation ) throws ScalarException
        {
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;

            //Quotation quotation = null;
            String salesOrderNo = null;



            try
            {
                conn = DataSource.getConnection ();

                String queryString = "SELECT Quotation_ID, Quotation_No, Customer_ID, Q_Customer_PO_Number, Q_Contact_Person, Q_Sales_Person, Q_Shipping_Method, Q_Shipping_Account, Q_Billing_Address_ID, Q_Shipping_Address_ID, Q_Comments, Q_Shipping_Instructions, Q_EndUser, Q_Status, Q_Booking_Date, Q_Is_Sample, Q_Is_Cancel, Q_Is_Pending, Q_Is_Shipped, Q_Entered_By_ID, Q_Entered_By_Name, Q_Sales_Tax_Type, Q_Sub_Total, Q_Sales_Tax, Q_Frieght_Tax, Q_Misc, Alarm_ID, Timestamp, Regional_Rep, Shipping_Terms, Delivery_Terms, Q_Misc_Name, Q_Show_Pro_Forma_Invoice, Expiry_Date, Q_Billing_Contact_Person, Q_Shipping_Contact_Person, Q_Sales_Rep, Q_Revision, Q_PO_Rev, Q_PO_Date, Q_ODM_ID , Q_Is_Customer_Ship_Address, datediff( day, current_timestamp, Expiry_Date ) as Is_Expired, f.Freight_Instructions, Q_SHIPPING_INSTRUCTIONS_ID, Q_Freight_Instructions_Id, Q_ENDCUSTOMER_ID, Q_QUOTE_TYPE, Q_APPLICATION, Q_ADD_NEW_APPLICATION, Q_NEW_END_USER, Q_NEW_END_CUSTOMER, Q_EXPECTED_PO_DATE, Q_PROJECT_NAME, Q_OPPORTUNITY_ID FROM Quotation_Table LEFT OUTER JOIN Freight_Instructions f ON f.FI_ID = Q_Freight_Instructions_Id WHERE Quotation_ID = ?";
                if ( isTransfer )
                {
                    queryString = "SELECT Quotation_ID, Quotation_No, Customer_ID, Q_Customer_PO_Number, Q_Contact_Person, Q_Sales_Person, Q_Shipping_Method, Q_Shipping_Account, Q_Billing_Address_ID, Q_Shipping_Address_ID, Q_Comments, Q_Shipping_Instructions, Q_EndUser, Q_Status, CURRENT_TIMESTAMP AS Q_Booking_Date, Q_Is_Sample, Q_Is_Cancel, Q_Is_Pending, Q_Is_Shipped, Q_Entered_By_ID, Q_Entered_By_Name, Q_Sales_Tax_Type, Q_Sub_Total, Q_Sales_Tax, Q_Frieght_Tax, Q_Misc, Alarm_ID, Timestamp, Regional_Rep, Shipping_Terms, Delivery_Terms, Q_Misc_Name, Q_Show_Pro_Forma_Invoice, Expiry_Date, Q_Billing_Contact_Person, Q_Shipping_Contact_Person, Q_Sales_Rep, Q_Revision, Q_PO_Rev, Q_PO_Date, Q_ODM_ID , Q_Is_Customer_Ship_Address, datediff( day, current_timestamp, Expiry_Date ) as Is_Expired, f.Freight_Instructions, Q_SHIPPING_INSTRUCTIONS_ID, Q_Freight_Instructions_Id, Q_ENDCUSTOMER_ID, Q_QUOTE_TYPE, Q_APPLICATION, Q_ADD_NEW_APPLICATION, Q_NEW_END_USER, Q_NEW_END_CUSTOMER, Q_EXPECTED_PO_DATE, Q_PROJECT_NAME, Q_OPPORTUNITY_ID  FROM Quotation_Table LEFT OUTER JOIN Freight_Instructions f ON f.FI_ID = Q_Freight_Instructions_Id WHERE Quotation_ID = ?";
                }

                pstmt = conn.prepareStatement ( queryString );
                pstmt.setInt ( 1,  salesOrderId );

                rs = pstmt.executeQuery ();
                if ( rs.next () == false )
                {
                    // TBD
                }
                else
                {
                    salesOrderNo = rs.getString ( "Quotation_No" );

                    //quotation.setQuotationNo ( salesOrderNo );
                    //quotation.setEnteredById ( rs.getString ( "Q_Entered_BY_ID" ) );
                    //quotation.setEnteredByName ( rs.getString ( "Q_Entered_BY_Name" ) );
                    quotation.setSalesTaxType ( rs.getString ( "Q_Sales_Tax_type" ) );

                    quotation.setSubTotalAsDouble (rs.getDouble("Q_Sub_Total"));
                    quotation.setSalesTaxAsDouble (rs.getDouble("Q_Sales_Tax"));
                    quotation.setFrieghtAsDouble (rs.getDouble("Q_Frieght_Tax"));
                    quotation.setMiscAsDouble (rs.getDouble("Q_Misc"));
                    quotation.setOrderTotalAsDouble (quotation.getSubTotalAsDouble() + quotation.getSalesTaxAsDouble() + quotation.getFrieghtAsDouble() + quotation.getMiscAsDouble());

                    //quotation.setEnteredDate ( StringUtils.formatDateToString ( rs.getDate ( "Timestamp" ) ) );
                    quotation.setRegionalRep ( rs.getString ( "Regional_Rep" ) );
                    quotation.setShippingTerms ( rs.getString ( "Shipping_Terms" ) );
                    quotation.setDeliveryTerms ( rs.getString ( "Delivery_Terms" ) );
                    quotation.setMiscName ( rs.getString ( "Q_Misc_Name" ) );
                    quotation.setExpiryDate ( StringUtils.formatDateToString ( rs.getDate ( "Expiry_Date" ), null ) );
                    quotation.setBillingContactPerson(rs.getString( "Q_Billing_Contact_Person" ));
                    quotation.setShippingContactPerson(rs.getString( "Q_Shipping_Contact_Person" ));
                    quotation.setSalesRep( rs.getString( "Q_Sales_Rep" ) );

                    quotation.setRevision( rs.getInt("Q_Revision"));
                    quotation.setPoRev( rs.getString("Q_PO_Rev"));
                    quotation.setPoDate( StringUtils.formatDateToString ( rs.getDate("Q_PO_Date")));
                    quotation.setOdmId( rs.getInt("Q_ODM_ID"));
                    quotation.setIsEndCustomerShipAddress( rs.getInt("Q_Is_Customer_Ship_Address") == 0 ? false : true ); //IsCustomerShipAddress in DB Field is mapping to the IsEndCustomerShipAddress. if isCustShipAddrs is TRUE Then IsEndCustShipAddrs is FALSE Hence storing FALSE in the property

                    quotation.setStatus( rs.getInt("Is_Expired") < 0 ? "Expired" : "Pending" );
                    quotation.setOdm(CustomerDAO.getCustomerName( null, quotation.getOdmId()));
                    quotation.setEndUserId ( EndUserDAO.getEndUserIdByName ( quotation.getEndUser (), quotation.getCustomerId () ) +"" ) ;

                    quotation.setFreightInstructions( rs.getString ( "Freight_Instructions" ) );
                    quotation.setShippingInstructionsId ( rs.getInt( "Q_SHIPPING_INSTRUCTIONS_ID") );
                    quotation.setFreightInstructionsId( rs.getInt ( "Q_Freight_Instructions_Id") );

                    quotation.setEndCustomerId( rs.getInt("Q_ENDCUSTOMER_ID") );
                    quotation.setQuoteType( rs.getString ( "Q_QUOTE_TYPE" ) );
                    quotation.setApplication( rs.getString( "Q_APPLICATION" ) );
                    quotation.setAddNewApplication( rs.getString ( "Q_ADD_NEW_APPLICATION" ) );
                    //quotation.setNewEndUserName( rs.getString ( "Q_NEW_END_USER" ) );
                    //quotation.setNewEndCustomerName( rs.getString ( "Q_NEW_END_CUSTOMER" ) );
                    quotation.setExpectedPoDate( StringUtils.formatDateToString ( rs.getDate("Q_EXPECTED_PO_DATE")));
                    quotation.setProjectName(rs.getString("Q_PROJECT_NAME"));
                    quotation.setOpportunityId(rs.getString("Q_OPPORTUNITY_ID"));
                    if ( rs != null )
                    {
                        try{ rs.close (); } catch ( SQLException excp ){ }
                        rs = null;
                    }
                    if ( pstmt != null )
                    {
                        try{ pstmt.close (); } catch ( SQLException excp ){ }
                        pstmt = null;
                    }
                    if ( conn != null )
                    {
                        try{ conn.close (); }catch ( SQLException excp ){ }
                        conn = null;
                    }
                    if ( quotation.getAddNewApplication() != null && quotation.getAddNewApplication().trim().length() > 0 )
                    {
                        quotation.setApplication( "-2" );//to select add new app
                    }
                    quotation.setEndCustomer ( CustomerDAO.getCustomerName( null, quotation.getEndCustomerId() ) );
                    Customer customer = CustomerDAO.loadCustomerById( quotation.getCustomerId() );
                    quotation.setCustomerName( customer.getCustomerName() );
                    quotation.setDistributor( customer.getIsDistributor() );
                    /*if(ConfigManager.isLoadQuotationQuestionnaire())
                    {
                        loadQuotationQuestionnaire(null, quotation);
                    }*/
                        ArrayList presentList = loadOrderLineItems ( null, quotation.getQuotationId () );

                    double total = 0.0f ;
                    double finalSalesTax = 0.0;
                    float taxvalue = 0.0f;

                    taxvalue = SalesOrderDAO.getTaxValue ( null, quotation.getSalesTaxType () );
                    Float ff = new Float ( taxvalue );
                    double tax = ff.doubleValue ();

                    for ( int i = 0; i < presentList.size (); i++ )
                    {
                        LineItem templineItem = ( LineItem ) presentList.get ( i );
                        total = total + templineItem.getExtendedPriceForQuote ();
                        if ( templineItem.getIsTaxable () )
                        {
                            finalSalesTax = finalSalesTax + ( templineItem.getExtendedPriceForQuote () * ( tax / 100 ) );
                        }
                    }
                    //quotation.setSalesTaxValue ( taxvalue );
                    //quotation.setFinalSubTotal ( total );
                    //quotation.setFinalSalesTax ( finalSalesTax );
                    //quotation.setFinalOrderTotal ( total + finalSalesTax + quotation.getMiscAsDouble () + quotation.getFrieghtAsDouble () );


                    quotation.setShippingMethodName ( quotation.getShippingMethod () );

                    //Address address = CustomerDAO.getAddress ( quotation.getBillingAddressId () );
                    Address address = CustomerDAO.getAddressWithContactPersonDetails( quotation.getBillingAddressId (), quotation.getBillingContactPerson(), quotation.getCustomerId() );
                    StringBuffer strBufferAddress = new StringBuffer ();

                    if ( address != null )
                    {
                        //quotation.setBillingAddressAsObject ( address );
                        quotation.setBillingAddress ( address.getAddressAsString ( ) );
                    }
                    int shippingAddressId = quotation.getShippingAddressId ();
                    if ( shippingAddressId < 0 )
                    {
                        shippingAddressId = SalesOrderDAO.getDefaultShippingAddressId( quotation.getCustomerId () );
                        quotation.setShippingAddressId ( shippingAddressId );
                        quotation.setShippingContactPerson (CustomerDAO.getAddress( shippingAddressId ).getContactPerson());
                    }
                    address = CustomerDAO.getAddressWithContactPersonDetails ( shippingAddressId, quotation.getShippingContactPerson() );
                    if ( address != null )
                    {
                       // quotation.setShippingAddressAsObject ( address );
                        quotation.setShippingAddress ( address.getAddressAsString ( ) );
                    }
                    String clientaddressId =  UserDAO.getClientAddressId();
                    String addressId = null;
                    String clientname = null;

                    int finalAddressId = 0;
                    if(clientaddressId != null)
                    {
                        StringTokenizer st = new StringTokenizer(clientaddressId,"@");
                        try
                        {
                            addressId = st.nextToken();
                            clientname = st.nextToken();
                        }catch(NoSuchElementException e)
                        {
                             clientname  = "";
                        }
                    }
                    if(addressId  != null)
                    {
                        finalAddressId = Integer.parseInt(addressId);
                    }
                    Address clientAddress= UserDAO.loadClientAddressDetails( null , finalAddressId );
                    //quotation.setClientAddressAsObject(clientAddress);
                    String clientAddress1 = SalesOrder.getClientAddressAsString(clientAddress);
                    //quotation.setClientAddress(clientAddress1);
                    //quotation.setClientName(clientname);
                    String contactPersonName = quotation.getContactPerson();
                    if( contactPersonName != null && contactPersonName.equals(quotation.getCustomerName()) )
                    {
                        Address addressObject = getQuotationAddress (null, quotation.getQuotationId() );
                        if(addressObject != null)
                        {
                            quotation.setContactPerson(quotation.getCustomerName());
                            quotation.setEmail ( addressObject.getEmail () );
                            quotation.setPhoneNumber ( addressObject.getPhoneNumber () );
                            quotation.setFaxNumber ( addressObject.getFaxNumber () );
                            quotation.setCellNumber ("");
                       }
                    }
                    else if(contactPersonName != null &&  contactPersonName.trim().length()!= 0)
                    {
                        ContactPerson contactPerson = null;
                        contactPerson = CustomerDAO.getContactPersonDetails (quotation.getCustomerId() , quotation.getContactPerson());
                        if(contactPerson != null)
                        {
                             quotation.setContactPerson( contactPerson.getContactPerson() );
                             quotation.setPhoneNumber( contactPerson.getPhoneNumber() );
                             quotation.setCellNumber( contactPerson.getCellNumber() );
                             quotation.setFaxNumber( contactPerson.getFaxNumber() );
                             quotation.setEmail( contactPerson.getEmail() );
                        }
                        else if(contactPerson == null)
                        {
                             contactPerson = new ContactPerson();
                             quotation.setContactPerson( "" );
                             quotation.setPhoneNumber( "" );
                             quotation.setCellNumber( "" );
                             quotation.setFaxNumber( "" );
                             quotation.setEmail( "" );
                        }
                    }
                    ArrayList contactPersons = CustomerDAO.getAllContactPersonsForCustomer( quotation.getCustomerId());
                    contactPersons.add(quotation.getCustomerName());
                    quotation.setContactPersons(contactPersons);
                   // quotation.setAllShippingTerms(SalesDAO.getAllShippingTerms());
                    quotation.setAllDeliveryTerms(SalesDAO.getAllDeliveryTerms( null ));
                    quotation.setContactPersonsForBillingAddress(CustomerDAO.getAllContactPersonsForAddress(  quotation.getBillingAddressId()));
                    quotation.setContactPersonsForShippingAddress(CustomerDAO.getAllContactPersonsForAddress( quotation.getShippingAddressId()));
                }
            }
            catch ( Exception sqlex )
            {
                sqlex.printStackTrace ();
                throw new ScalarException ( "Could not get Quotation Info from database", sqlex );
            }
            finally
            {
                if ( rs != null )
                {
                    try
                    {
                        rs.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( pstmt != null )
                {
                    try
                    {
                        pstmt.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
                if ( conn != null )
                {
                    try
                    {
                        conn.close ();
                    }
                    catch ( SQLException excp )
                    {
                    }
                }
            }
            //return quotation;
        }

    public static ArrayList loadOrderLineItems ( DBTransaction dbTransaction, int salesOrderId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList lineItems = new ArrayList ();

        boolean success = false;

        if ( salesOrderId == -1 )
            return null;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            String queryString = "SELECT QOL.*, Quotation_No FROM Quotation_Order_LineItem QOL, Quotation_Table QT WHERE QT.Quotation_ID = ? and QT.Quotation_id = QOL.Quotation_id and QOL.Transfer <> 0 order by QOL.QLI_Line_Item_No";

            pstmt = conn.prepareStatement ( queryString );
            pstmt.setInt ( 1, salesOrderId );

            rs = pstmt.executeQuery ();
            LineItem lineItem = null;

            while ( rs.next () )
            {
                lineItem = new LineItem ();

                lineItem.setSalesOrderId ( salesOrderId );
                lineItem.setLineItemId ( rs.getInt ( "LineItem_ID" ) );
                lineItem.setLineItemNo ( rs.getInt ( "QLI_Line_Item_No" ) );
                lineItem.setPoLineItemNo ( rs.getInt ( "QLI_PO_Line_Item_No" ) );
                lineItem.setPoLineItemNo ( rs.getInt ( "QLI_PO_Line_Item_No" ) );
                lineItem.setProductId ( rs.getInt ( "Product_ID" ) );
                lineItem.setQuantity ( rs.getInt ( "QLI_Qty" ) );
                lineItem.setLeadTime ( rs.getString("QLI_lead_time"));
                lineItem.setUnitPriceAsDouble ( rs.getDouble ( "QLI_Unit_Cost" ) );
                lineItem.setAmountAsDouble ( rs.getDouble ( "QLI_Amount" ) );
                lineItem.setRequestDate ( StringUtils.formatDateToString ( rs.getDate ( "QLI_Requested_Date" ), null ) );
                lineItem.setIsTaxable ( ( rs.getInt ( "QLI_Taxable" ) == 0 ) ? true : false );
                lineItem.setSalesOrderNo ( rs.getString ( "Quotation_No" ) );
                lineItem.setLineItemDescription ( rs.getString ( "QLI_Description" ) );

                lineItem.setIsApproved ( ( ( rs.getInt ( "Is_Approved" ) == 0 ) ? true : false ) );
                lineItem.setReqPrice( rs.getDouble( "REQUESTED_PRICE" ) );
                lineItem.setCustResalePrice( rs.getDouble( "CUST_RESALE_PRICE" ) );
                lineItem.setSuggResalePrice( rs.getDouble( "SUGG_RESALE_PRICE" ) );
                lineItem.setMinOrderQty( rs.getInt( "MIN_ORDER_QTY" ) );
                lineItem.setPreviousQuantity ( lineItem.getQuantity () );

                lineItems.add ( lineItem );
            }
            success = true;
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get present lineItems Info from database", sqlex );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                        conn.commit ();
                    else
                        conn.rollback ();
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }

        }
        for (int i = 0; i < lineItems.size(); i++) {
            LineItem lineItem = (LineItem) lineItems.get(i);
            loadMarketingAndMfgPartNumbers ( lineItem );
        }
        return lineItems;
    }

    public static ArrayList loadOrderLineItemsWithCustomerPartNo ( int salesOrderId, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList lineItems = new ArrayList ();
        boolean success = false;

        if ( salesOrderId == -1 )
            return null;

        try
        {
            conn = DataSource.getConnection ();

            pstmt = conn.prepareStatement ( "SELECT QOL.*, QT.Quotation_No FROM Quotation_Order_LineItem QOL, Quotation_Table QT WHERE QT.Quotation_ID = ? and QT.Quotation_id = QOL.Quotation_id and QOL.Transfer <> 0 and ISNULL(QOL.QLI_IS_CANCEL,1) <> 0 order by QOL.QLI_Line_Item_No" );
            pstmt.setInt ( 1, salesOrderId );
            rs = pstmt.executeQuery ();
            LineItem lineItem = null;

            while ( rs.next () )
            {
                lineItem = new LineItem ();

                lineItem.setSalesOrderId ( salesOrderId );
                lineItem.setLineItemId ( rs.getInt ( "LineItem_ID" ) );
                lineItem.setLineItemNo ( rs.getInt ( "QLI_Line_Item_No" ) );
                lineItem.setPoLineItemNo ( rs.getInt ( "QLI_PO_Line_Item_No" ) );
                lineItem.setPoLineItemNo ( rs.getInt ( "QLI_PO_Line_Item_No" ) );
                lineItem.setProductId ( rs.getInt ( "Product_ID" ) );
                lineItem.setQuantity ( rs.getInt ( "QLI_Qty" ) );
                lineItem.setUnitPriceAsDouble ( rs.getDouble ( "QLI_Unit_Cost" ) );
                lineItem.setAmountAsDouble ( rs.getDouble ( "QLI_Amount" ) );
                lineItem.setRequestDate ( StringUtils.formatDateToString ( rs.getDate ( "QLI_Requested_Date" ), null ) );
                lineItem.setIsTaxable ( ( rs.getInt ( "QLI_Taxable" ) == 0 ) ? true : false );
                lineItem.setSalesOrderNo ( rs.getString ( "Quotation_No" ) );
                lineItem.setLineItemDescription ( rs.getString ( "QLI_Description" ) );
                lineItem.setCustomerPartNo ( rs.getString ( "QLI_Customer_PartNumber" ) ); //Added by Sarada to set customer part no
                lineItem.setIsApproved ( ( ( rs.getInt ( "Is_Approved" ) == 0 ) ? true : false ) );

                lineItem.setPreviousQuantity ( lineItem.getQuantity () );
                lineItem.setQuotedQuantity( rs.getInt("QLI_Quoted_Qty") );
                lineItem.setLeadTime(rs.getString("QLI_LEAD_TIME"));
                lineItem.setQuotedDate( StringUtils.formatDateToString ( rs.getDate ( "QLI_Quoted_Date" ), null )  );
                lineItem.setReqPrice( rs.getDouble( "REQUESTED_PRICE" ) );
                lineItem.setCustResalePrice( rs.getDouble( "CUST_RESALE_PRICE" ) );
                lineItem.setSuggResalePrice( rs.getDouble( "SUGG_RESALE_PRICE" ) );
                lineItem.setMinOrderQty( rs.getInt( "MIN_ORDER_QTY" ) );
                
                QuotationDAO.loadMarketingAndMfgPartNumbers ( lineItem );

                lineItems.add ( lineItem );
            }

            success = true;
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get present lineItems Info from database", sqlex );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                        conn.commit ();
                    else
                        conn.rollback ();
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }

        }

        return lineItems;
    }


    public static LineItem loadMarketingAndMfgPartNumbers ( LineItem lineItem ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        if ( lineItem == null )
            return null;

        try
        {
            conn = DataSource.getConnection ();

            pstmt = conn.prepareStatement ( "SELECT Part_Number, P_Marketing_Part_Number, QLI_description as P_Comments FROM Product p, quotation_order_lineitem qol WHERE  qol.lineitem_id = ? and p.Product_ID = qol.product_id" );
            pstmt.setInt ( 1, lineItem.getLineItemId () );

            rs = pstmt.executeQuery ();

            while ( rs.next () )
            {

                lineItem.setManufacturingPartNo ( rs.getString ( "Part_Number" ) );
                lineItem.setMarketingPartNo ( rs.getString ( "P_Marketing_Part_Number" ) );
                lineItem.setDescription ( rs.getString ( "P_Comments" ) );

            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get part number for this product", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return lineItem;
    }

    public static int saveQuotation ( DBTransaction dbTransaction, Quotation quotation ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        boolean success = false;
        int quotationId = -1;

        quotationId = quotation.getQuotationId ();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            if ( quotationId == -1 )
            {
                String queryString = "INSERT INTO Quotation_Table (Customer_ID, Q_Customer_PO_Number, Q_Contact_Person, Q_Sales_Person, Q_Shipping_Method, Q_Shipping_Account, Q_Billing_Address_ID, Q_Shipping_Address_ID, Q_Comments, Q_Shipping_Instructions, Q_EndUser, Q_Status, Q_Booking_date, Q_Is_Sample, Q_Is_Cancel, Q_Is_Pending, Q_Is_Shipped, Q_Entered_By_ID, Q_Entered_By_Name, Q_Sales_Tax_Type, Q_Sub_Total, Q_Sales_Tax, Q_Frieght_Tax, Q_Misc,Quotation_No,Regional_Rep, Shipping_Terms, Delivery_terms,Q_Misc_Name,timestamp,Expiry_Date,Q_Billing_Contact_Person,Q_Shipping_Contact_Person,Q_Sales_Rep, Q_Revision, Q_PO_Rev, Q_PO_Date, Q_ODM_ID, Q_Is_Customer_Ship_Address, Q_Freight_Instructions,Q_SHIPPING_INSTRUCTIONS_ID, Q_Freight_Instructions_Id, Q_ENDCUSTOMER_ID, Q_QUOTE_TYPE, Q_APPLICATION, Q_ADD_NEW_APPLICATION, Q_NEW_END_USER, Q_NEW_END_CUSTOMER, Q_EXPECTED_PO_DATE, Q_PROJECT_NAME, Q_OPPORTUNITY_ID ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                pstmt = conn.prepareStatement ( queryString );
                pstmt.setInt ( 1, quotation.getCustomerId () );
                pstmt.setString ( 2, quotation.getCustomerPO () );
                pstmt.setString ( 3, quotation.getContactPerson () );
                pstmt.setString ( 4, quotation.getSalesPerson () );
                pstmt.setString ( 5, ( ( quotation.getNewShippingMethod () != null ) && ( quotation.getNewShippingMethod ().length () > 0 ) ) ? quotation.getNewShippingMethod () : quotation.getShippingMethodName () );
                pstmt.setString ( 6, quotation.getShippingAccount () );
                pstmt.setInt ( 7, quotation.getBillingAddressId () );
                pstmt.setInt ( 8, quotation.getShippingAddressId () );
                pstmt.setString ( 9, quotation.getComments () );
                pstmt.setString ( 10, quotation.getShippingInstructions () );
                pstmt.setString ( 11, quotation.getEndUser () );
                pstmt.setString ( 12, "PENDING" );
                pstmt.setDate ( 13, StringUtils.getSQLDate ( quotation.getQuotationDate () ) );
                pstmt.setInt ( 14, quotation.getIsSample () ? 0 : 1 );
                pstmt.setInt ( 15, quotation.isCancel () ? 0 : 1 );
                pstmt.setInt ( 16, quotation.isPending () ? 0 : 1 );
                pstmt.setInt ( 17, quotation.isShipped () ? 0 : 1 );
                pstmt.setString ( 18, quotation.getEnteredById () );
                pstmt.setString ( 19, quotation.getEnteredByName () );
                pstmt.setString ( 20, quotation.getSalesTaxType () );
                pstmt.setDouble ( 21, quotation.getSubTotalAsDouble () );
                pstmt.setDouble ( 22, quotation.getSalesTaxAsDouble () );
                pstmt.setDouble ( 23, quotation.getFrieghtAsDouble () );
                pstmt.setDouble ( 24, quotation.getMiscAsDouble () );
                pstmt.setString ( 25, quotation.getQuotationNo () );
                pstmt.setString ( 26, quotation.getRegionalRep () );
                pstmt.setString ( 27, quotation.getShippingTerms () );
                pstmt.setString ( 28, quotation.getDeliveryTerms () );
                pstmt.setString ( 29, quotation.getMiscName () );
                pstmt.setDate   ( 30, StringUtils.getSQLDate ( quotation.getExpiryDate () ) );
                pstmt.setString (31, quotation.getBillingContactPerson());
                pstmt.setString (32, quotation.getShippingContactPerson());
                pstmt.setString (33, quotation.getSalesRep() );
                pstmt.setInt    ( 34, 0 );
                pstmt.setString ( 35, quotation.getPoRev() );
                pstmt.setDate   ( 36, StringUtils.getSQLDate ( quotation.getPoDate() ));
                pstmt.setInt    ( 37, quotation.getOdmId() );
                pstmt.setInt    ( 38, quotation.getIsEndCustomerShipAddress() == false ?  0 : 1 ); //IsEndCustomerShipAddress is mapping to the IsCustomerShipAddress in DB Field. if IsEndCustShipAddrs is TRUE Then isCustShipAddrs is FALSE Hence storing FALSE in the DB
                pstmt.setString ( 39, quotation.getFreightInstructions() );
                pstmt.setString ( 40, quotation.getShippingInstructionsId() > 0 ? quotation.getShippingInstructionsId() +"" : null);
                pstmt.setString ( 41, quotation.getFreightInstructionsId() > 0 ? quotation.getFreightInstructionsId() +"" : null);
                pstmt.setInt    ( 42, quotation.getEndCustomerId() );
                pstmt.setString ( 43, ("-1".equals(quotation.getQuoteType()) || "-2".equals(quotation.getQuoteType())) ? null : quotation.getQuoteType() );
                pstmt.setString ( 44, ("-1".equals(quotation.getApplication()) || "-2".equals(quotation.getApplication())) ? null : quotation.getApplication() );
                pstmt.setString ( 45, quotation.getAddNewApplication() != null ? quotation.getAddNewApplication().trim() : null  );
                pstmt.setString ( 46, quotation.getNewEndUserName() == null ? null : quotation.getNewEndUserName().trim() );
                pstmt.setString ( 47, quotation.getNewEndCustomerName() == null ? null : quotation.getNewEndCustomerName().trim() );
                pstmt.setDate   ( 48, StringUtils.getSQLDate ( quotation.getExpectedPoDate() ));
                pstmt.setString ( 49, quotation.getProjectName());
                pstmt.setString ( 50, quotation.getOpportunityId());
                //pstmt.setInt    ( 49, 0);
                pstmt.executeUpdate ();
                pstmt.close ();
                pstmt = null;

                pstmt = conn.prepareStatement ( "SELECT @@IDENTITY as Quotation_Id" );
                rs = pstmt.executeQuery ();

                if ( rs.next () )
                {
                    quotationId = rs.getInt ( "Quotation_Id" );
                }

                pstmt.close ();

            }
            else
            {

                cstmt = conn.prepareCall ( "{ Call sp_change_quotation_proc(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }" );
                cstmt.setString ( 1, quotation.getCustomerPO () );
                cstmt.setString ( 2, quotation.getContactPerson () );
                cstmt.setString ( 3, quotation.getSalesPerson () );
                cstmt.setString ( 4, quotation.getShippingMethodName () );
                cstmt.setString ( 5, quotation.getShippingAccount () );
                cstmt.setInt    ( 6, quotation.getBillingAddressId () );
                cstmt.setInt    ( 7, quotation.getShippingAddressId () );
                cstmt.setString ( 8, quotation.getComments () );
                cstmt.setString ( 9, quotation.getShippingInstructions () );
                cstmt.setString ( 10, quotation.getEndUser () );
                cstmt.setString ( 11, quotation.getSalesTaxType () );
                cstmt.setDouble ( 12, quotation.getSubTotalAsDouble () );
                cstmt.setDouble ( 13, quotation.getSalesTaxAsDouble () );
                cstmt.setDouble ( 14, quotation.getFrieghtAsDouble () );
                cstmt.setDouble ( 15, quotation.getMiscAsDouble () );
                cstmt.setString ( 16, quotation.getQuotationNo () );
                cstmt.setString ( 17, quotation.getRegionalRep () );
                cstmt.setString ( 18, quotation.getShippingTerms () );
                cstmt.setString ( 19, quotation.getDeliveryTerms () );
                cstmt.setDate   ( 20, StringUtils.getSQLDate ( quotation.getQuotationDate () ) );
                cstmt.setInt    ( 21, quotation.getQuotationId () );
                cstmt.setString ( 22, quotation.getReason () );
                cstmt.setString ( 23, quotation.getPhoneNumber () );
                cstmt.setString ( 24, quotation.getFaxNumber () );
                cstmt.setString ( 25, quotation.getEmail () );
                cstmt.setString ( 26, quotation.getMiscName () );
                cstmt.setDate   ( 27, StringUtils.getSQLDate ( quotation.getExpiryDate () ) );
                cstmt.setString ( 28, quotation.getBillingContactPerson());
                cstmt.setString ( 29, quotation.getShippingContactPerson());
                cstmt.setString ( 30, quotation.getCellNumber());
                cstmt.setString ( 31, quotation.getSalesRep() );
                cstmt.setString ( 32, quotation.getPoRev() );
                cstmt.setDate ( 33, StringUtils.getSQLDate ( quotation.getPoDate() ));
                cstmt.setInt  ( 34, quotation.getOdmId() );
                cstmt.setInt  ( 35, quotation.getIsEndCustomerShipAddress() == false ? 0 : 1 ); //IsEndCustomerShipAddress is mapping to the IsCustomerShipAddress in DB Field. if IsEndCustShipAddrs is TRUE Then isCustShipAddrs is FALSE Hence storing FALSE in the DB
                cstmt.setString ( 36, quotation.getFreightInstructions() );
                cstmt.setString ( 37, quotation.getShippingInstructionsId() > 0 ? quotation.getShippingInstructionsId() +"" : null);
                cstmt.setString ( 38, quotation.getFreightInstructionsId() > 0 ? quotation.getFreightInstructionsId() +"" : null);
                cstmt.setInt  ( 39, quotation.getEndCustomerId() );
                cstmt.setString ( 40, ("-1".equals(quotation.getQuoteType()) || "-2".equals(quotation.getQuoteType())) ? null : quotation.getQuoteType() );
                cstmt.setString ( 41, ("-1".equals(quotation.getApplication()) || "-2".equals(quotation.getApplication())) ? null : quotation.getApplication() );
                cstmt.setString ( 42, quotation.getAddNewApplication() );
                cstmt.setString ( 43, quotation.getNewEndUserName() );
                cstmt.setString ( 44, quotation.getNewEndCustomerName() );
                cstmt.setDate   ( 45, StringUtils.getSQLDate ( quotation.getExpectedPoDate() ));
                cstmt.setString (46, quotation.getProjectName());
                cstmt.setString (47, quotation.getOpportunityId());

                cstmt.execute ();
            }

            success = true;
        }
        catch ( Exception se )
        {
            se.printStackTrace ();
            System.out.println ( "Exception in QuotationDAO:SaveQuotation" + se );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( cstmt != null )
            {
                try
                {
                    cstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return quotationId;
    }

    public static void saveQuotationQuestionnaire ( DBTransaction dbTransaction, Quotation quotation ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        boolean success = false;
        int quotationId = -1;

        quotationId = quotation.getQuotationId ();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }
            String queryString;
            
            if ( quotationId != -1 )
            {
                pstmt = conn.prepareStatement (" delete from Quotation_Questionnaire_Table where QUOTATION_ID=?" );
                pstmt.setInt ( 1, quotation.getQuotationId() );
                pstmt.execute();
                pstmt.close ();
                pstmt = null;
            }

                queryString = "INSERT INTO Quotation_Questionnaire_Table (CUSTOMER_ID, QUOTATION_ID , END_CUSTOMER_ID, ODM_ID, QUOTATON_TYPE, QUOTATION_NO, CURRENT_PRICE, CURRENT_PRICE_UNITS, CURRENT_PRICE_UNITS_TYPE, CURRENT_PRICE_OTHERS, TARGET_PRICE, TARGET_PRICE_UNITS, TARGET_PRICE_UNITS_TYPE, TARGET_PRICE_OTHERS, HISTORY_PRICE_INFO, IS_CONTRACT_PRICE, CONTRACT_PRICE_FILE_PATH, CONTRACT_PRICE_CHANGE_REASON, PREVIOUS_COMMITMENTS_QUANTITIES, PREVIOUS_COMMITMENTS_PRICING, PREVIOUS_COMMITMENTS_ADDTIONAL_INFO, IS_PREVIOUS_QUOTATION,PREVIOUS_QUOTATION_DETAILS,  PRICE_DISCOUNT_REASON_1, PRICE_DISCOUNT_REASON_2,PRICE_DISCOUNT_REASON_COMMNETS, IS_REBATE, REBATE_VALUE, REBATE_OPPERTUNITY, REBATE_GOAL, REBATE_TARGET_VALUE, REBATE_TARGET, REBATE_VERIFIED_BY, COMPETATIOR_1, COMPETATIOR_1_OTHERS, COMPETATIOR_1_PRICE, COMPETATIOR_1_PRICEUNIT, COMPETATIOR_1_COMMENTS, COMPETATIOR_2, COMPETATIOR_2_OTHERS, COMPETATIOR_2_PRICE, COMPETATIOR_2_PRICEUNIT, COMPETATIOR_2_COMMENTS, DISTRIBUTOR_MARGIN, MARGIN_DOLLARS, TOTAL_PREV_MARGINS, MARGIN_REASON, Q_NEW_END_USER, Q_NEW_END_CUSTOMER, PREVIOUS_QUOTATION_NO, COMPETATIOR1PRICEUNITSTYPE, COMPETATIOR1PRICEUNITOTHERS,  COMPETATIOR2PRICEUNITSTYPE, COMPETATIOR2PRICEUNITOTHERS, REBATE_TARGET_GOAL_TIME_FRAME,  REBATE_TARGET_GOAL_TIME_FRAME_OTHERS,  TIMESTAMP ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)";
                pstmt = conn.prepareStatement ( queryString );
                pstmt.setInt    ( 1, quotation.getCustomerId () );
                pstmt.setInt    ( 2, quotation.getQuotationId() );
                pstmt.setInt    ( 3, quotation.getOdmId());
                pstmt.setInt    ( 4, quotation.getEndCustomerId());
                pstmt.setString ( 5,  quotation.getQuotationType() );
                pstmt.setString ( 6, quotation.getQuotationNo() );
                pstmt.setFloat  ( 7, quotation.getCurrentPrice()  );
                pstmt.setInt    ( 8, quotation.getCurrentPriceUnits() );
                pstmt.setString ( 9, quotation.getCurrentPriceUnitsType() );
                pstmt.setString ( 10, quotation.getCurrentPriceOthers() );
                pstmt.setFloat  ( 11, quotation.getTargetPrice()  );
                pstmt.setInt    ( 12, quotation.getTargetPriceUnits() );
                pstmt.setString ( 13, quotation.getTargetPriceUnitsType() );
                pstmt.setString ( 14, quotation.getTargetPriceOthers());
                pstmt.setString ( 15, quotation.getHistoryPricesInfo());// isCancel () ? 0 : 1 );
                pstmt.setInt    ( 16, quotation.isContractPriceFlag() ? 0 : 1 );
                pstmt.setString ( 17, (quotation.isContractPriceFlag() ? quotation.getContractPriceFileName(): ""));
                pstmt.setString ( 18, (quotation.isContractPriceFlag() ? quotation.getContractPriceChangedReason(): "") );
                pstmt.setString ( 19, quotation.getPreviousCommitments()  );
                pstmt.setString ( 20, quotation.getPreviousCommitmentsPrice() );
                pstmt.setString ( 21, quotation.getCommitmentAddtionalInfo() );
                pstmt.setInt    ( 22, quotation.isPreviousQuotation()? 0 : 1  );
                pstmt.setString ( 23, (quotation.isPreviousQuotation() ? quotation.getPreviousQuotationDetails() : "") );
                pstmt.setString ( 24, StringUtils.concatStrings( quotation.getPriceDiscountReason(), Mailer.EMAIL_SEPARATOR ) );
                pstmt.setString ( 25, StringUtils.concatStrings( quotation.getPriceDiscountReason(), Mailer.EMAIL_SEPARATOR )  );
                pstmt.setString ( 26, quotation.getPriceDiscountComments());
                pstmt.setInt    ( 27, quotation.isRebate() ? 0:1 );
                pstmt.setFloat  ( 28, (quotation.isRebate() ? quotation.getRebateValue(): 0) );
                pstmt.setString ( 29, (quotation.isRebate() ? quotation.getRebateOpertunity(): null) );
                pstmt.setString ( 30, (quotation.isRebate() ? quotation.getRebateGoal(): "-1") );
                pstmt.setFloat  ( 31, (quotation.isRebate() ? quotation.getRebateTargetValue():0) );
                pstmt.setString ( 32, (quotation.isRebate() ? quotation.getRebateTarget():"-1") );
                pstmt.setString (33, (quotation.isRebate() ? quotation.getRebateVerifiedBy(): null));
                pstmt.setString (34, "Other".equalsIgnoreCase(quotation.getCompetatior1()) ? quotation.getCompetatior1Others(): quotation.getCompetatior1());
                pstmt.setString (35, "Other".equalsIgnoreCase(quotation.getCompetatior1()) ? quotation.getCompetatior1Others(): "" );
                pstmt.setFloat  ( 36, quotation.getCompetatior1Price() );
                pstmt.setString ( 37, quotation.getCompetatior1PriceUnit() );
                pstmt.setString ( 38, quotation.getCompetatior1Comments());
                pstmt.setString (39, "Other".equalsIgnoreCase(quotation.getCompetatior2()) ? quotation.getCompetatior2Others(): quotation.getCompetatior2() );
                pstmt.setString (40, "Other".equalsIgnoreCase(quotation.getCompetatior2()) ? quotation.getCompetatior2Others(): "" );
                pstmt.setFloat  ( 41, quotation.getCompetatior2Price() );
                pstmt.setString ( 42, quotation.getCompetatior2PriceUnit() );
                pstmt.setString ( 43, quotation.getCompetatior2Comments());
                pstmt.setFloat  ( 44, quotation.getDistributorMargin() );
                pstmt.setFloat  ( 45, quotation.getMarginDollars() );
                pstmt.setString ( 46, quotation.getTotalPrevMargins() );
                pstmt.setString ( 47, quotation.getMarginReason() );
                pstmt.setString ( 48, quotation.getNewEndUserName() == null ? null : quotation.getNewEndUserName().trim() );
                pstmt.setString ( 49, quotation.getNewEndCustomerName() == null ? null : quotation.getNewEndCustomerName().trim() );
                pstmt.setString ( 50, quotation.getPrevQuotationNo() );
                pstmt.setString ( 51, quotation.getCompetatior1PriceUnitsType());
                pstmt.setString ( 52, quotation.getCompetatior1PriceUnitOthers() );
                pstmt.setString ( 53, quotation.getCompetatior2PriceUnitsType());
                pstmt.setString ( 54, quotation.getCompetatior2PriceUnitOthers() );
                pstmt.setString ( 55, quotation.getRebateTargetGoalTimeFrame());
                pstmt.setString ( 56, quotation.getRebateTargetGoalTimeFrameOthers() );

                pstmt.executeUpdate ();
                pstmt.close ();
                pstmt = null;
                success = true;
                boolean comFlag =false;
                if ( "Other".equalsIgnoreCase(quotation.getCompetatior1())   )
                {

                    if (quotation.getCompetatior1Others().trim() != null && quotation.getCompetatior1Others().trim() != "" )
                    {
                        pstmt = conn.prepareStatement ("select * from  COMPETITORS_TABLE where COMPETATIOR_NAME = ?" );
                        pstmt.setString ( 1, quotation.getCompetatior1Others() );
                        rs = pstmt.executeQuery ();
                        if ( !rs.next())
                        {
                            comFlag= true;
                        }
                        pstmt.close();
                        rs.close();
                        if (comFlag)
                        {
                            pstmt = conn.prepareStatement ("insert into COMPETITORS_TABLE (COMPETATIOR_NAME) VALUES(?)" );
                            pstmt.setString ( 1, quotation.getCompetatior1Others() );
                            pstmt.execute();
                            pstmt.close();
                        }
                        comFlag=false;
                    }
                }
                if("Other".equalsIgnoreCase(quotation.getCompetatior2()))
                {
                    if (quotation.getCompetatior2Others().trim() != null && quotation.getCompetatior2Others().trim() != "")
                    {
                        pstmt = conn.prepareStatement ("select * from  COMPETITORS_TABLE where COMPETATIOR_NAME = ?" );
                        pstmt.setString ( 1, quotation.getCompetatior2Others() );
                        rs = pstmt.executeQuery ();
                        if ( !rs.next())
                        {
                            comFlag= true;
                        }
                        pstmt.close();
                        rs.close();
                        if (comFlag)
                        {
                            pstmt = conn.prepareStatement ("insert into COMPETITORS_TABLE (COMPETATIOR_NAME) VALUES(?)" );
                            pstmt.setString ( 1, quotation.getCompetatior2Others() );
                            pstmt.execute();
                            pstmt.close();
                        }
                        comFlag=false;
                    }
                }
        }

        catch ( Exception se )
        {
            se.printStackTrace ();
            System.out.println ( "Exception in QuotationDAO:SaveQuotationQuestionnaire" + se );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( cstmt != null )
            {
                try
                {
                    cstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }
    public static void insertLineItems ( DBTransaction dbTransaction, int salesOrderId, ArrayList lineItems, String userId, int isOnlyNewLineItemChange  ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int status = 0;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            Iterator items = lineItems.iterator ();

            while ( items.hasNext () )
            {
                if ( cstmt != null )
                {
                    cstmt.close ();
                    cstmt = null;
                }

                LineItem lineItem = ( LineItem ) items.next ();

                if( items.hasNext()) {
                    status = 0;
                } else {
                    status = 1;
                }

                cstmt = conn.prepareCall ( "{ Call sp_insert_quotation_lineItem_proc (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?, ? ) }" );

                cstmt.setInt    ( 1, salesOrderId );
                cstmt.setInt    ( 2, lineItem.getLineItemNo () );
                cstmt.setInt    ( 3, lineItem.getProductId () );
                cstmt.setString ( 4, lineItem.getLineItemDescription () );
                cstmt.setInt    ( 5, lineItem.getQuantity () );
                cstmt.setDouble ( 6, lineItem.getUnitPriceAsDouble () );
                cstmt.setDouble ( 7, lineItem.getAmountAsDouble () );
                cstmt.setDate   ( 8, StringUtils.getSQLDate ( lineItem.getRequestDate () ) );
                cstmt.setString ( 9, userId );
                cstmt.setInt    ( 10, lineItem.getIsTaxable () ? 0 : 1 );
                cstmt.setInt    ( 11, lineItem.getPoLineItemNo () );
                cstmt.setInt    ( 12, lineItem.getQuotedQuantity() );
                cstmt.setString ( 13, "0".equals ( lineItem.getLeadTime() ) ? null : lineItem.getLeadTime() );
                cstmt.setDate   ( 14, StringUtils.getSQLDate ( lineItem.getQuotedDate() ) );
                cstmt.setInt    ( 15, isOnlyNewLineItemChange );
                cstmt.setInt    ( 16, status );
                cstmt.setString ( 17, "-2".equals( lineItem.getCustomerPartNo() ) ? ( (lineItem.getNewCustomerPartNo() == null) ? "" : lineItem.getNewCustomerPartNo().trim() ) : lineItem.getCustomerPartNo() );
                cstmt.setDouble ( 18, lineItem.getReqPrice() );
                cstmt.setDouble ( 19, lineItem.getCustResalePrice() );
                cstmt.setDouble ( 20, lineItem.getSuggResalePrice() );
                cstmt.setInt    ( 21, "-2".equals( lineItem.getCustomerPartNo() ) ? 0 : 1 );
                cstmt.setInt    ( 22, lineItem.getMinOrderQty());
                cstmt.execute ();
            }

            success = true;

        }
        catch ( Exception se )
        {
            throw new ScalarException ( "Could not insert lineItems Info into database", se );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit ();
                        }
                        else
                        {
                            conn.rollback ();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( cstmt != null )
            {
                try
                {
                    cstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    public static String getAutomatedQuoteNo () throws ScalarException
    {
        //int count = 1;
        String newQuoteNumber = generateNewAutomatedQuotationNo(); //generateAutomatedQuoteNo( count );

        return newQuoteNumber;
    }

    /**
     * This method will increment and return the quote number. The parameter specifies
     * the value by which the existing quote number has to be incremented by, to get the
     * new sales order number. Normally this value is given as 1 as the new quote number
     * is always one greater than the existing quote number. This method is useful when
     * the incremented quote number is already existing and we need the next incremented
     * quote number, which can be achieved by passing the value as 2, 3, 4 and so on...
     *
     * @param incrementBy
     * @return salesOrderNumber
     * @throws ScalarException
     *
     * @deprecated we are not using this method since new pattern has been introduced.
     * @see QuotationDAO#generateNewAutomatedQuotationNo()
     */
    public static String generateAutomatedQuoteNo( int incrementBy ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int quoteId = -1;
        String quoteNo = null;
        String finalQuoteNo = null;
        Format formatter = null;
        String number = null;
        String quotePrefix = null;
        int quoteLength = 0;

        try
        {
            conn  = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT Quotation_Prefix, Quotation_Length FROM Sales_Info" );
            rs = pstmt.executeQuery();
            if( rs.next())
            {
                quotePrefix = rs.getString ( "Quotation_Prefix" );
                quoteLength = rs.getInt ( "Quotation_Length" );
            }
            rs.close();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement ("select Quotation_Id, Quotation_No from Quotation_Table where Quotation_Id = (select max(Quotation_Id) from Quotation_Table)");
            rs    = pstmt.executeQuery();

            if (rs.next())
            {
                quoteNo = rs.getString ("Quotation_No");
            }
            else
            {
                quoteNo = "0";
            }

            try
            {

                if(quoteNo != null)
                {
                    int indexValue = quoteNo.indexOf("-");
                    number = quoteNo.substring(indexValue+1);
                    String no = (Long.parseLong (number) + incrementBy ) + "";
                    int noLength = no.length();
                    int totalLength = quoteLength - noLength ;
                    if( totalLength > 0 )
                    {
                        for(int  i = 0; i < totalLength ; i++  )
                        {
                            no = "0"+no;
                        }
                    }

                    if( quotePrefix != null && quotePrefix.trim().length() != 0 )
                    {
                        finalQuoteNo = quotePrefix+"-"+no ;
                    }
                    else
                    {
                        finalQuoteNo = no;
                    }
                }
            }
            catch (NumberFormatException nfe)
            {
                finalQuoteNo = (rs.getInt ("Quotation_Id") + incrementBy )+"" ;
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Automated Quote No from database", sqlex);
        }

        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return finalQuoteNo;
    }


    public static void saveChangeLineItems ( DBTransaction dbTransaction, int salesOrderId, ArrayList lineItems, String userId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;

        try
        {
            Iterator items = lineItems.iterator ();
            ArrayList itemsToInsert = new ArrayList ();
            ArrayList itemsToChange = new ArrayList ();
            ArrayList itemsToCancel = new ArrayList ();

            while ( items.hasNext () )
            {
                LineItem lineItem = ( LineItem ) items.next ();
                int lineItemId = lineItem.getLineItemId ();

                if ( lineItemId != -1 )
                {
                    lineItem.setLineItemId ( lineItemId );
                    if ( lineItem.getIsCancel() )
                    {
                        itemsToCancel.add ( lineItem );   
                    }
                    else
                    {
                        itemsToChange.add ( lineItem );
                    }
                }
                else if ( lineItemId == -1 )
                {
                    itemsToInsert.add ( lineItem );
                }
            }


            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            int increment_status = 0;
            int lineitem_updated_status = 0;
            if ( itemsToChange.size () != 0 )
            {

                if ( cstmt != null )
                {
                    cstmt.close ();
                    cstmt = null;
                }
                int loop_status = 0;
                Iterator list = itemsToChange.iterator ();

                while ( list.hasNext () )
                {
                    if ( cstmt != null )
                    {
                        cstmt.close ();
                        cstmt = null;
                    }
                    cstmt = conn.prepareCall ( "{ call sp_Change_Quotation_LineItem_Proc (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
                    LineItem lineItem = ( LineItem ) list.next ();
                    if(list.hasNext())
                    {
                        loop_status = 0;
                    }
                    else
                    {
                        loop_status = 1;
                    }
                    cstmt.setInt    ( 1, lineItem.getLineItemId () );
                    cstmt.setInt    ( 2, salesOrderId );
                    cstmt.setDate   ( 3, StringUtils.getSQLDate ( lineItem.getRequestDate () ) );
                    cstmt.setInt    ( 4, lineItem.getQuantity () );
                    cstmt.setDouble ( 5, lineItem.getUnitPriceAsDouble () );
                    cstmt.setDouble ( 6, lineItem.getAmountAsDouble () );
                    cstmt.setString ( 7, userId );
                    cstmt.setInt    ( 8, lineItem.getIsTaxable () ? 0 : 1 );
                    cstmt.setString ( 9, lineItem.getLineItemDescription () );
                    cstmt.setInt    ( 10, lineItem.getQuotedQuantity() );
                    cstmt.setString ( 11, "0".equals( lineItem.getLeadTime() ) ? null : lineItem.getLeadTime() );
                    cstmt.setDate   ( 12, StringUtils.getSQLDate ( lineItem.getQuotedDate()) );
                    cstmt.setInt    ( 13, loop_status );
                    cstmt.setInt    ( 14, lineitem_updated_status );
                    cstmt.setString ( 15, "-2".equals( lineItem.getCustomerPartNo() ) ? ( (lineItem.getNewCustomerPartNo() == null) ? "" : lineItem.getNewCustomerPartNo().trim() ) : lineItem.getCustomerPartNo() );
                    cstmt.registerOutParameter ( 16, java.sql.Types.INTEGER );
                    cstmt.setDouble ( 17, lineItem.getReqPrice() );
                    cstmt.setDouble ( 18, lineItem.getCustResalePrice() );
                    cstmt.setDouble ( 19, lineItem.getSuggResalePrice() );
                    cstmt.setInt    ( 20, lineItem.getProductId () );
                    cstmt.setInt    ( 21, "-2".equals( lineItem.getCustomerPartNo() ) ? 0 : 1 );
                    cstmt.setInt    ( 22, lineItem.getMinOrderQty());

                    cstmt.execute ();
                    increment_status = cstmt.getInt(16);
                    if (increment_status == 1)
                    {
                        lineitem_updated_status = increment_status;
                    }

                }
            }
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
                conn = null;
            }
            if ( cstmt != null )
            {
                try { cstmt.close (); } catch ( SQLException ignore ) {}
                cstmt = null;
            }
            if ( itemsToInsert.size () != 0 )
            {
                int isOnlyNewLineItemChange = 1;

                if (lineitem_updated_status == 1)
                {
                    isOnlyNewLineItemChange = 2;
                }
                else
                {
                     isOnlyNewLineItemChange = 1;
                }
                insertLineItems ( dbTransaction, salesOrderId, itemsToInsert, userId, isOnlyNewLineItemChange  );
            }
            if ( itemsToCancel.size() > 0 )
            {
                cancelLineItems ( dbTransaction, itemsToCancel, userId );
            }
            success = true;
        }
        catch ( Exception se )
        {
            se.printStackTrace ();
            throw new ScalarException ( "Could not update lineItems Info into database", se );
        }
        finally
        {
            if ( dbTransaction == null && !success && conn != null )
            {
                try
                {
                    conn.rollback ();
                }
                catch ( SQLException ignore )
                {

                }
            }
            if ( cstmt != null )
            {
                try
                {
                    cstmt.close ();
                }
                catch ( SQLException ignore )
                {

                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {

                }
            }

        }
    }


    public static ArrayList loadQuotationsToChange(String salesOrderId, String customerId, String manufacturingPartNo, String viewExpired, String userId, String customerPO, String endCustomer, String fromDate, String toDate) throws ScalarException
    {
        if ( salesOrderId == null )
        {
            return null;
        }
        else
        {
            salesOrderId = salesOrderId.trim() ;
        }

        Connection conn = null;
        CallableStatement cstmt = null;
        ArrayList ordersList = new ArrayList ();
        ResultSet rset = null;

        String queryString = "";

        try
        {
            conn = DataSource.getConnection ();
            int loadExpired = 0;
            if ( viewExpired != null )
            {
                loadExpired = 1;
            }
            if ( salesOrderId != null && salesOrderId.trim ().equals ( "" ) )
            {
                salesOrderId = "0";
            }

            if ( ( customerId != null ) && customerId.trim ().equals ( "" ) )
            {
                customerId = "0";
            }

            if ( manufacturingPartNo != null && manufacturingPartNo.trim ().equals ( "" ) )
            {
                manufacturingPartNo = "0";
            }

            cstmt = conn.prepareCall ( "{call sp_load_quotations_to_change(?, ?, ?, ?, ?, ?, ?, ?, ?) }" );
            cstmt.setInt    ( 1, loadExpired );
            cstmt.setString ( 2, salesOrderId );
            cstmt.setString ( 3, manufacturingPartNo );
            cstmt.setInt    ( 4, Integer.parseInt ( customerId ) );
            cstmt.setString ( 5, userId );
            cstmt.setString ( 6, customerPO );
            cstmt.setString ( 7, endCustomer );
            cstmt.setString ( 8, fromDate );
            cstmt.setString ( 9, toDate );

            rset = cstmt.executeQuery ();
            Orders orders = null;

            while ( rset.next () )
            {
                orders = new Orders ();
                orders.setSalesOrderId ( rset.getInt ( "Quotation_ID" ) );
                orders.setSalesOrderNo ( rset.getString ( "Quotation_No" ) );
                orders.setCustomerPO ( rset.getString ( "Q_Customer_PO_Number" ) );
                //orders.setCustomerName ( CustomerDAO.loadCustomerName ( rset.getInt ( "Customer_ID" ) ) );
                orders.setBookingDate ( StringUtils.formatDateToString ( rset.getDate ( "Q_Booking_Date" ), null ) );
                orders.setExpiryDate ( StringUtils.formatDateToString ( rset.getDate ( "Expiry_Date" ), null ) );
                orders.setIsExpire ( rset.getInt ( "is_expired" ) < 0 ? true : false );
                orders.setIsApproved ( ( rset.getInt ( "Is_Approved" ) == 0 ? true : false ) );
                orders.setIsTotatallyApproved ( ( rset.getInt ( "Is_Totally_Approved" ) == 0 ? true : false ) );
                orders.setIsTotallyTransferred ( ( rset.getInt ( "Is_Totally_Transferred" ) == 0 ? true : false ) );
                orders.setCustomerName( rset.getString ( "CUSTOMER_NAME" ) );
                orders.setProductName( rset.getString ( "MFG_PART_NUMBER" ) );
                orders.setEndCustomerName( rset.getString ( "END_CUSTOMER" ) );
                orders.setEndUserName( rset.getString ( "END_USER" ) );
                orders.setQty( rset.getInt ( "QTY" ) );
                orders.setUnitPrice( rset.getDouble( "UNIT_COST" ) );
                orders.setSuggResalePrice( rset.getDouble ( "SUGG_RESALE_PRICE" ) );
                orders.setQuoteType ( rset.getString ( "QUOTE_TYPE" ) );
                orders.setNewEndUserName(rset.getString ( "Q_NEW_END_USER" ));
                orders.setNewEndCustomerName(rset.getString ( "Q_NEW_END_CUSTOMER" ));
                orders.setCancel ( ( rset.getInt ( "Is_cancelled" ) == 0 ? true : false ) );
                ordersList.add ( orders );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not OrdersList from database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( cstmt != null )
            {
                try
                {
                    cstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return ordersList;
    }

    private static String loadSearchString ( String salesOrderId, String customerId, String manufacturingPartNo ) throws ScalarException
    {
        String search_string = "";

        if ( ( salesOrderId != null ) && ( salesOrderId.trim ().length () != 0 ) )
        {
            search_string = search_string + " and Quotation_Table.Quotation_No like '" + StringUtils.formatToHandleQuote ( salesOrderId.trim () ) + "%'";
        }

        if ( ( manufacturingPartNo != null ) && ( manufacturingPartNo.trim ().length () > 0 ) )
        {
            search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductIdForSearch ( manufacturingPartNo );
        }

        if ( ( customerId != null ) && ( !"0".equals ( customerId ) && ( customerId.length () > 0 ) ) )
        {
            search_string = search_string + " and Customer_ID = " + Integer.parseInt ( customerId );
        }

        return search_string + " ORDER BY Quotation_No";
    }

    public static int loadMaxLineItemNo ( int salesOrderId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        int lineItemNo = 0;

        try
        {
            conn = DataSource.getConnection ();

            String queryString = "SELECT isNULL(MAX(QLI_LINE_ITEM_NO),0) + 1 [LINEITEM_NO] FROM QUOTATION_ORDER_LINEITEM WHERE Quotation_ID = ?";
            pstmt = conn.prepareStatement ( queryString );
            pstmt.setInt ( 1, salesOrderId );

            rs = pstmt.executeQuery ();

            if ( rs.next () )
            {
                lineItemNo = rs.getInt ( "LINEITEM_NO" );
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get max lineItem Number for this orderId", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }

        return lineItemNo;
    }

    public static void approveQuotationOrderLineItems ( DBTransaction dbTransaction, int salesOrderId, ArrayList lineItems ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }


            for ( int i = 0; i < lineItems.size (); i++ )
            {
                LineItem lineItem = ( LineItem ) lineItems.get ( i );

                int isApproved = 0;

                if ( lineItem.getIsApproved () )
                {
                    pstmt = conn.prepareStatement ( "UPDATE Quotation_Order_LineItem set Is_Approved = ?, QLI_LEAD_TIME = ? where LineItem_Id=?" );
                    pstmt.setInt ( 1, isApproved );
                    pstmt.setString ( 2, "0".equals ( lineItem.getLeadTime() ) ? null : lineItem.getLeadTime() );
                    pstmt.setInt ( 3, lineItem.getLineItemId () );
                    pstmt.executeUpdate ();

                    pstmt.close();
                    pstmt = null;

                    pstmt = conn.prepareStatement ( "UPDATE Quotation_Change_Order_LineItem SET Is_Approved = ? WHERE LineItem_Id = ? AND Change_Number = ( SELECT ISNULL( MAX( Change_Number ), 0 ) FROM Quotation_Change_Order_LineItem WHERE LineItem_ID = ? )" );
                    pstmt.setInt ( 1, isApproved );
                    pstmt.setInt ( 2, lineItem.getLineItemId () );
                    pstmt.setInt ( 3, lineItem.getLineItemId () );
                    pstmt.executeUpdate ();
                }
            }
            success = true;
        }
        catch ( Exception se )
        {

            se.printStackTrace ();
            throw new ScalarException ( "Could not update Approval Status of the Quotation into database", se );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( success == true )
                    {
                        conn.commit ();
                    }
                    else
                    {
                        conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }

        }
    }

    //Updates the Quote Subtotal with new subTotal subtracting the salesorder subtotal from quote sub total.
    public static void updateQuoteSubTotal ( DBTransaction dbTransaction, int quotationId, double subTotal ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( "UPDATE Quotation_Table set Q_Sub_Total = (Q_Sub_Total - ?)  where Quotation_Id = ?" );
            pstmt.setDouble ( 1, subTotal );
            pstmt.setInt ( 2, quotationId );
            pstmt.executeUpdate ();
            success = true;
        }
        catch ( Exception se )
        {

            se.printStackTrace ();
            throw new ScalarException ( "Could not update SubTotal of the Quotation into database", se );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( success == true )
                    {
                        conn.commit ();
                    }
                    else
                    {
                        conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }

        }
    }

    public static void transferQuotationLineItems ( DBTransaction dbTransaction, int salesOrderId, int quotationId, ArrayList lineItems, String userId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }
            for ( int i = 0; i < lineItems.size (); i++ )
            {
                LineItem lineItem = ( LineItem ) lineItems.get ( i );
                int isApproved = 0;
                if ( lineItem.getIsApproved () )
                {
                    isApproved = 1;
                }
                if ( lineItem.getIsTransfer () )
                {
                    pstmt = conn.prepareStatement ( "{ CALL SP_TRANSFER_QUOTATION_LINEITEM( ?, ?, ?, ?, ?, ? ) }" );
                    pstmt.setInt( 1, salesOrderId );
                    pstmt.setInt ( 2, quotationId );
                    pstmt.setInt( 3, lineItem.getQuotationLineItemId() );
                    pstmt.setInt( 4, lineItem.getQuantity() );
                    pstmt.setInt( 5, lineItem.getIsTaxable() == true ? 0 : 1 );
                    pstmt.setString( 6, userId );

                    pstmt.executeUpdate ();
                }
            }
            success = true;
        }
        catch ( Exception se )
        {

            se.printStackTrace ();
            throw new ScalarException ( "Could not update Approval Status of the Quotation into database", se );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( success == true )
                    {
                        conn.commit ();
                    }
                    else
                    {
                        conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }

        }
    }

    public static Alarm createAlarm ( DBTransaction dbTransaction, User user, Quotation quotation, String host ) throws ScalarException
    {
        Alarm newAlarm = null;

        int quotationId         = quotation.getQuotationId ();
        String quotationNo      = quotation.getQuotationNo ();
        String comments         = quotation.getComments ();
        AlarmUser to            = null;
        ArrayList recipients    = new ArrayList ();
        ArrayList approversList = new ArrayList ();
        ArrayList list          = null;
        User userBean           = null;

        try
        {
            // CREATE ORIGINATOR AND RECIPIENTS
            AlarmUser from = new AlarmUser ( user.getUserId (), user.getEmail () );

            // SET EXPIRE DATE
            Calendar cal = Calendar.getInstance ();
            cal.add ( Calendar.DAY_OF_YEAR, ConfigManager.getSalesOrderExpiresAfterDays () );
            Date expiresOn = cal.getTime ();

            //This needs to be modified, if there is no need to send mail, if system automatically approves
            approversList.add ( QuotationDAO.loadQuotationApprovers( dbTransaction ) );
            for ( int i = 0; i < approversList.size(); i++ )
            {
                list = ( ArrayList ) approversList.get ( i );
                for ( int j = 0; j < list.size (); j++ )
                {
                    userBean = ( User ) list.get ( j );
                    to = new AlarmUser ( userBean.getUserId (), userBean.getEmail () );
                    recipients.add ( to );   
                }
            }

            newAlarm = new Alarm ( "Quote Notification - Quote Number: " + quotationNo, QUOTE_ALARM, from );
            newAlarm.setPriority ( newAlarm.ALARM_PRIORITY_HIGH );
            newAlarm.setExpiresOn ( expiresOn.getTime () );

            newAlarm.setRecipients ( recipients );

            // ACTIVATE
            newAlarm.activate ( dbTransaction );

            updateQuotationFields ( dbTransaction, "UPDATE Quotation_Table SET Q_Is_Pending=0, Alarm_ID = " + newAlarm.getId () + " WHERE Quotation_ID = " + quotationId );

            // to remove users who are currently disabled and for whom Notifications not required for mail notifications
            recipients.clear ();
            for ( int i = 0; i < approversList.size (); i++ )
            {
                list = ( ArrayList ) approversList.get ( i );
                for ( int j = 0; j < list.size (); j++ )
                {
                    userBean = ( User ) list.get ( j );                    
                    if ( userBean.isQuoteAckMail() && userBean.getDisabled() == false )
                    {
                        to = new AlarmUser ( userBean.getUserId (), userBean.getEmail () );
                        recipients.add ( to );
                    }
                }
            }
            // set the new recipients to the alarm
            newAlarm.setRecipients ( recipients );
        }
        catch ( Exception ex )
        {
            ex.printStackTrace ();
            throw new ScalarException ( "Could not create alarm", ex );
        }

        return newAlarm;
    }

    public static void updateQuotationFields ( DBTransaction dbTransaction, String sqlString ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            pstmt = conn.prepareStatement ( sqlString );

            pstmt.executeUpdate ();

            success = true;
        }
        catch ( Exception se )
        {
            se.printStackTrace ();
            throw new ScalarException ( "Could not Update Quotation Fields from database", se );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                            conn.commit ();
                        else
                            conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

    }

    public static Alarm updateAlarmForChangeQuotation ( DBTransaction dbTransaction, String reason, int quotationId, User user ) throws ScalarException
    {
        Alarm oldAlarm  = null;
        int alarmId     = 0;

        try
        {
            alarmId = getAlarmIdByQuotationId( dbTransaction, quotationId );

            if( alarmId > 0 )
            {
                //Load all the existing alarm
                oldAlarm = AlarmDAO.loadQuotationAlarmById( dbTransaction, alarmId );

                oldAlarm.setLastUpdatedBy ( user.getUserId () );

                Comment comment = new Comment ( user.getUserId (), reason );
                oldAlarm.addComment ( dbTransaction, comment );

                //this is added because when approver approvs also, it is sending with originator name
                oldAlarm.setOriginator ( new AlarmUser ( user.getUserId (), user.getEmail () ) );

                
            }
            else
            {
                throw new ScalarException ("Could not get alarmId for quotationId " + quotationId + " from database");
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace ();
            throw new ScalarException ( "Could not get alarmId for alarm from database", sqle );
        }

        return oldAlarm;
    }

    public static void updateAlarmAndSendMailWithChangeSummary( String comments, int quotationId, boolean isApproved, User user, String changeSummary ) throws ScalarException
	{
        int alarmId             = 0;
        Alarm oldAlarm          = null;
        ArrayList approversList = new ArrayList();
        AlarmUser to            = null;
        ArrayList recipientList = new ArrayList();
        ArrayList list          = null;
        User userBean           = null;
        String quotationStatus  = null;

        try
        {
            alarmId = getAlarmIdByQuotationId( null, quotationId );

            if( alarmId > 0 )
            {
                //Load all the existing alarm
                oldAlarm = AlarmDAO.loadQuotationAlarmById( null, alarmId );

                //Set the user response according to the approve/reject
                if( oldAlarm == null )
                {
                    throw new ScalarException ("Could not get alarmId for the quotation " + quotationId + " from database");
                }

                if( isApproved )
                {
                    oldAlarm.setUserResponse( user.getUserId(), Alarm.ALARM_RESPONSE_ACCEPT, comments );
                }
                else
                {
                    oldAlarm.setUserResponse( user.getUserId(), Alarm.ALARM_RESPONSE_REJECT, comments );
                }
                oldAlarm.setLastUpdatedBy( user.getUserId() );

                //this is added because when approver approvs also, it is sending with originator name
                oldAlarm.setOriginator( new AlarmUser( user.getUserId(), user.getEmail() ) );

                approversList.add( QuotationDAO.loadQuotationApprovers( null ) );

                for( int i = 0; i < approversList.size (); i++ )
                {
                    list = ( ArrayList ) approversList.get ( i );

                    for ( int j = 0; j < list.size (); j++ )
                    {
                        userBean = ( User ) list.get ( j );
                        
                        if ( userBean.isQuoteAckMail() && userBean.getDisabled () == false )
                        {
                            if ( user.getUserId().equalsIgnoreCase(userBean.getUserId() ) && isApproved )
                            {
                                to = new AlarmUser ( userBean.getUserId(), userBean.getEmail(), Alarm.ALARM_RESPONSE_ACCEPT );
                            }
                            else
                            {
                                to = new AlarmUser ( userBean.getUserId (), userBean.getEmail () );
                            }
                            recipientList.add ( to );
                        }
                    }
                }

                oldAlarm.setRecipients ( recipientList );
                try
                {
                    Quotation quotation = QuotationDAO.loadQuotation( quotationId + "", false );
                    if ( quotation.getSalesPerson() != null && quotation.getSalesPerson().length() > 0 )
                    {
                        SalesPerson salesPerson = NewCustomerDAO.getSalesPersonDetails( null, quotation.getSalesPerson() );
                        if ( salesPerson != null && salesPerson.isQuoteAckMail() )
                        {
                            if ( salesPerson.getAddress() != null && salesPerson.getAddress().getEmail() != null && salesPerson.getAddress().getEmail().trim().length() > 0 )
                            {
                                oldAlarm.addRecipient ( new AlarmUser( salesPerson.getSalesPerson (), salesPerson.getAddress().getEmail().trim() ) );
                            }
                        }
                    }

                    quotationStatus = QuotationDAO.getQuotationStatus( quotation.getQuotationNo(), "0", "0", "1", user.getUserId(), "", "0", "", "" );

                    oldAlarm.setSubject( "Quote Notification - Quote Number: " + quotation.getQuotationNo() + " - Customer: " + quotation.getCustomerName() + " - Status: " + quotationStatus );

                    oldAlarm.notifyAllRecipients( quotation.getHtmlViewWithChangeSummary(changeSummary ) );
                    //oldAlarm.notifyAllRecipients ( Quotation.getHtmlViewWithChangeSummary ( quotationId, changeSummary ) );
                }
                catch( Exception e ) { }
            }
            else
            {
                throw new ScalarException( "Could not get alarmId for the quotationId " + quotationId + " from database" );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not get alarmId for quotationId from database", sqle );
        }

    }


    public static boolean isQuotationNoExists ( String quotationNo ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean isExists = false;

        try
        {
            conn = DataSource.getConnection ();

            pstmt = conn.prepareStatement ( "SELECT Quotation_No From Quotation_Table WHERE Quotation_No = ?" );
            pstmt.setString ( 1, quotationNo );
            rs = pstmt.executeQuery ();

            if ( rs.next () == false )
            {
                isExists = false;
            }
            else
            {
                isExists = true;
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Quotation_No for Sales Order Info from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }

        return isExists;
    }

    public static void loadClientAddress(Quotation quotation)  throws ScalarException
    {
        String clientaddressId =  UserDAO.getClientAddressId();
        String addressId = null;
        String clientname = null;

        int finalAddressId = 0;
        if(clientaddressId != null)
        {
            StringTokenizer st = new StringTokenizer(clientaddressId,"@");
            try
            {
                addressId = st.nextToken();
                clientname = st.nextToken();
            }catch(NoSuchElementException e)
            {
                 clientname  = "";
            }
        }
        if(addressId  != null)
        {
            finalAddressId = Integer.parseInt(addressId);
        }
        Address clientAddress= UserDAO.loadClientAddressDetails( null , finalAddressId );

        quotation.setClientAddressAsObject(clientAddress);
        String clientAddress1 = SalesOrder.getClientAddressAsString(clientAddress);
        quotation.setClientAddress(clientAddress1);

    }

    public static void updateAddress(DBTransaction dbTransaction, int quotationId, String phoneNumber, String faxNumber, String email) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.quotation.insertQuotationAddressDetails"));
            pstmt.setString (1, phoneNumber);
            pstmt.setString (2, faxNumber);
            pstmt.setString (3, email);
            pstmt.setInt (4, quotationId);
            pstmt.execute();

            success = true;
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update quotation details info into database", sqle);
        }
        finally
        {
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
          }

     }

    //To get Quotation ph,fa,email details from Quotation_Address table if contact person is not there
    public static Address getQuotationAddress(DBTransaction dbTransaction, int quotationId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Address address = null;

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.customer.getQuotationAddress"));
            pstmt.setInt (1, quotationId);

            rs = pstmt.executeQuery();

            if (rs.next() == false)
            {
                // Address not found, ???Throw exception
            }
            else
            {
                address = new Address();
                address.setPhoneNumber(rs.getString("Phone_Number"));
                address.setFaxNumber(rs.getString("Fax_Number"));
                address.setEmail(rs.getString("Email"));
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Address from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return address;
    }

    //To update the sales tax of the quotation with latest tax value .
    public static void updateQuoteSalesTax ( DBTransaction dbTransaction, String quotationSalesTax, ArrayList lineItems, int quotationId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }

            String salesTaxType = "0";
            double salesTax = 0.0;

            float taxvalue = SalesOrderDAO.getTaxValue ( null, quotationSalesTax );
            Float ff = new Float ( taxvalue );
            double tax = ff.doubleValue ();

            for ( int i = 0; i < lineItems.size (); i++ )
            {
                LineItem lineItem = ( LineItem ) lineItems.get ( i );
                if ( !(lineItem.getIsClose ()) )
                {
                    if ( lineItem.getIsTaxable () )
                    {
                        salesTaxType = quotationSalesTax;
                        if ( !(lineItem.getIsTransfer()) )
                        {
                            salesTax = salesTax + ( lineItem.getAmountAsDouble () * ( tax / 100 ) );
                        }
                        else if ( lineItem.getPreviousQuantity() != lineItem.getQuantity() )
                        {
                            salesTax = salesTax + ( lineItem.getPreviousQuantity() - lineItem.getQuantity() ) * ( lineItem.getUnitPriceAsDouble()) *  ( tax / 100 );
                        }
                    }
                }
            }
            pstmt = conn.prepareStatement ( "UPDATE Quotation_Table set Q_Sales_Tax_Type = ?, Q_Sales_Tax = ? where Quotation_Id = ?" );
            pstmt.setString ( 1, salesTaxType );
            pstmt.setDouble ( 2, salesTax );
            pstmt.setInt( 3, quotationId );
            pstmt.executeUpdate ();
            success = true;
        }
        catch ( Exception se )
        {
            se.printStackTrace ();
            throw new ScalarException ( "Could not update SubTotal of the Quotation into database", se );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                try
                {
                    if ( success == true )
                    {
                        conn.commit ();
                    }
                    else
                    {
                        conn.rollback ();
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException ignore )
                {
                }
            }

        }
    }

    public static String getQuotationIdByAlarmId ( String alarmId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        String quotationId = null;

        if ( alarmId == null )
            return null;

        try
        {
            conn = DataSource.getConnection ();

            String queryString = "SELECT Quotation_ID FROM Quotation_Table WHERE Alarm_ID = ?";

            pstmt = conn.prepareStatement ( queryString );
            pstmt.setInt ( 1, Integer.parseInt ( alarmId ) );

            rs = pstmt.executeQuery ();

            if ( rs.next () )
            {
                quotationId = rs.getString ( "Quotation_ID" );
            }

        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Quotation ID from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return quotationId;
    }

    //To load the Quotation Revision Number
    public static int loadRevisionNumber(DBTransaction dbTransaction, int quotationId) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int revision = 0;

        try
        {
            if (dbTransaction == null) {
                  conn = DataSource.getConnection();
            }else {
                  conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.quotation.getRevisionNumber"));
            pstmt.setInt (1, quotationId);

            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                 revision = rs.getInt("Q_Revision");
            }

        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Revision from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return revision;
    }

    public static String getNotificationChangeSummary(DBTransaction dbTransaction, int salesOrderId, ArrayList lineItems) throws ScalarException
    {
        HashMap map = loadOrderLineItemsHashMap(dbTransaction,salesOrderId);
        Iterator list = lineItems.iterator();
        StringBuffer changeSummary = new StringBuffer();
        boolean oldTaxValue = false;

        while(list.hasNext())
        {
            LineItem newItem = (LineItem)list.next();
            LineItem oldItem = (LineItem)map.get(new Integer(newItem.getLineItemId()));
            boolean newTaxValue = newItem.getIsTaxable();
            try
            {
                oldTaxValue = oldItem.getIsTaxable();
            }catch(NullPointerException e){
                 oldTaxValue = false;
            }

            String taxValue = "";

            if( newTaxValue )
                taxValue = " Selected ";
            else
                taxValue = " Deselected ";

            if(oldItem != null)
            {
                if( oldItem.getPoLineItemNo() != newItem.getPoLineItemNo() )
                    changeSummary.append("<br> PoLineItem is changed from <b><I> " +oldItem.getPoLineItemNo() + "</I></b>  to <b><I> " +newItem.getPoLineItemNo() + "</I></b>  for line item " + newItem.getLineItemNo());
                if( newTaxValue != oldTaxValue )
                    changeSummary.append("<br> Tax is <b><I> " +taxValue + "</I></b>  for line item " + newItem.getLineItemNo());
                if(oldItem.getQuantity() != newItem.getQuantity())
                    changeSummary.append("<br>Quantity is changed from <b><I>" + oldItem.getQuantity() + "</I></b>  to <b><I> " + newItem.getQuantity() + " </I></b>for line item " + newItem.getLineItemNo());
                if(!oldItem.getRequestDate().equals(newItem.getRequestDate()))
                    changeSummary.append("<br>Requested date is changed from <b><I>" + oldItem.getRequestDate() + "</I></b>  to <b><I>" + newItem.getRequestDate() + "</I></b> for line item " + newItem.getLineItemNo());
                if( oldItem.getUnitPriceAsDouble() != newItem.getUnitPriceAsDouble())
                     changeSummary.append("<br>Unit price is changed from <b><I>" + oldItem.getUnitPriceAsString() + " </I></b> to <b><I>" + newItem.getUnitPriceAsString() + "</I></b> for line item " + newItem.getLineItemNo());
            }
            else
                changeSummary.append("<br>LineItem " + newItem.getLineItemNo() + "  has been added");
        }
        return changeSummary.toString();
   }

   public static HashMap loadOrderLineItemsHashMap(DBTransaction dbTransaction, int salesOrderId) throws ScalarException
   {
       HashMap map = new HashMap();
       ArrayList line_items = loadOrderLineItems( dbTransaction, salesOrderId );
       if(line_items != null)
       {
           Iterator list = line_items.iterator();
           while(list.hasNext())
           {
               LineItem item = (LineItem)list.next();
               map.put(new Integer(item.getLineItemId()),item);
           }
       }
       return map;
   }

    public static String loadQuotationNo ( DBTransaction dbTransaction, int quotationId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String quotationNo      = null;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT QUOTATION_NO FROM QUOTATION_TABLE WHERE QUOTATION_ID = ?" );
            pstmt.setInt ( 1, quotationId );
            rs = pstmt.executeQuery();

            if ( rs.next () )
            {
                quotationNo = rs.getString( "QUOTATION_NO" ) ;
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Quotation_No from database", sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close (); } catch ( SQLException excp ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close (); } catch ( SQLException excp ) { }
            }
            if ( conn != null )
            {
                try { conn.close (); } catch ( SQLException excp ) { }
            }
        }

        return quotationNo;
    }


    public static void closeLineItems( DBTransaction dbTransaction, ArrayList closedLineItems, String userId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        Iterator items = closedLineItems.iterator();

        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement("UPDATE QUOTATION_ORDER_LINEITEM SET TRANSFER = 0 WHERE LINEITEM_ID = ?");

            while( items.hasNext() )
            {
                LineItem lineItem = ( LineItem ) items.next();
                pstmt.setInt( 1, lineItem.getLineItemId() );
                pstmt.execute();
            }
        }
        catch (Exception se)
        {
            se.printStackTrace();
            throw new ScalarException ("Could not insert lineItems Info into database", se);
        }
        finally
        {
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static ArrayList loadChangedLineItems ( DBTransaction dbTransaction, int quotationId, int isModifiedOrTransferred ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        LineItem lineItem       = null;
        ArrayList lineItems     = new ArrayList();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection ();
            }
            else
            {
                conn = dbTransaction.getConnection ();
            }
            pstmt = conn.prepareStatement ( "SELECT * FROM FN_LOAD_MODIFIED_QUOTATION_ORDER_LINEITEMS( ?, ? ) ORDER BY LINEITEM_ID, CHANGE_NUMBER" );
            pstmt.setInt( 1, quotationId );
            pstmt.setInt( 2, isModifiedOrTransferred );        //means only modified line items details not transferred line items details

            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                lineItem = new LineItem();

                lineItem.setQuotationLineItemId ( rs.getInt( "LINEITEM_ID" ) );
                lineItem.setQuotationLineItemNo ( rs.getString( "LINEITEM_NO" ) );
                lineItem.setPoLineItemNo        ( rs.getInt( "PO_LINEITEM_NO" ) );
                lineItem.setProductId           ( rs.getInt("PRODUCT_ID") );
                lineItem.setManufacturingPartNo ( rs.getString("PART_NUMBER") );
                lineItem.setCustomerPartNo      ( rs.getString("CUSTOMER_PART_NO") );
                lineItem.setRequestDate         ( StringUtils.formatDateToString( rs.getDate( "REQUESTED_DATE" ), null ) );
                lineItem.setBookingDate         ( StringUtils.formatDateToString( rs.getDate( "BOOKING_DATE" ), null ) );

                lineItem.setQuantity            ( rs.getInt( "QUANTITY" ) );
                lineItem.setLeadTime            ( rs.getString("LEAD_TIME")  );
                lineItem.setUnitPriceAsDouble   ( rs.getDouble( "UNIT_PRICE" ) );
				lineItem.setAmountAsDouble      ( rs.getDouble( "AMOUNT" ) );
                lineItem.setIsTaxable           ( ( rs.getInt( "TAXABLE" ) == 0 ) ? true : false );
                lineItem.setIsApproved          ( ( rs.getInt ( "IS_APPROVED" ) == 0 ) ? true : false );

                lineItem.setSalesOrderNo        ( rs.getString ( "SALESORDER_NO" ) );
                lineItem.setSalesOrderLineItemNo( rs.getString ( "SO_LINEITEM_NO" ) );
                lineItem.setReqPrice            ( rs.getDouble ( "REQUESTED_PRICE" ) );
                lineItem.setCustResalePrice     ( rs.getDouble ( "CUST_RESALE_PRICE" ) );
                lineItem.setSuggResalePrice     ( rs.getDouble ( "SUGG_RESALE_PRICE" ) );
                lineItem.setMinOrderQty         ( rs.getInt( "MIN_ORDER_QTY" ) );

                lineItems.add( lineItem );
            }
        }
        catch ( Exception se )
        {
            se.printStackTrace ();
            throw new ScalarException ( "Could not get previous quotation line item details from database", se );
        }
        finally
        {
            if ( rs != null )
            {
                try { rs.close (); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close (); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close (); } catch ( SQLException ignore ) { }
            }
        }

        return lineItems;
    }
    /**
     * This method will return the auto generated Quotation number to be used to create new Quotation. <br>
     * Auto Generated Quotation Number will be in the format: <b>&lt;QO PREFIX&gt;-&lt;yMMdd&gt;&lt;seq&gt; </b><br>
     * where <b>&lt;QO PREFIX&gt;</b> - Quotation Order Prefix which is set in the company setup. if it is empty then hyphen(-) is not added to the auto Quotation number.<br>
     * <b>&lt;yMMdd&gt;</b> - single digit year(y) Two digit month(MM) Two digit day(dd). month range will be 01-12 and day will be 01-31<br>
     * <b>&lt;seq&gt;</b> - sequence number. range will be 00 - 99<br>
     * For Ex: QO-7011700 --> QO - Prefix <br>
     *                    --> 7 - year(2007)<br>
     *                    --> 01 - month(January)<br>
     *                    --> 17 - day<br>
     *                    --> 00 - sequence</br>
     * when QO prefix is not given then the auto QO number will be: <b>7011700</b>
     * @return returns the auto generated Quotation number.
     */
    public static String generateNewAutomatedQuotationNo() throws ScalarException
    {
        String newQuotationNumber = null;
        String sequence = "00";
        String quotationNumber = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        String quotationPrefix = null;
        int quotationLength = 0;
        Calendar cl	= Calendar.getInstance();
        newQuotationNumber = StringUtils.formatDateToString(cl.getTime(), "yyMMdd" );
        newQuotationNumber = newQuotationNumber.substring(1);

        try
        {
            conn = DataSource.getConnection();
            /*pstmt = conn.prepareStatement( "SELECT Quotation_Prefix, Quotation_Length FROM Sales_Info" );
            rset = pstmt.executeQuery();
            if( rset.next())
            {
                quotationPrefix = rset.getString("Quotation_Prefix");
                quotationLength = rset.getInt("Quotation_Length");
            }

            try { rset.close(); rset = null; } catch ( SQLException ignore ) {}
            try { pstmt.close(); pstmt = null; } catch ( SQLException ignore ) {}*/

            pstmt = conn.prepareStatement( "SELECT  TOP 1 QUOTATION_NO FROM QUOTATION_TABLE WHERE LEN(SUBSTRING(QUOTATION_NO,CHARINDEX('-',QUOTATION_NO)+1,LEN(QUOTATION_NO))) = 7 ORDER BY QUOTATION_ID DESC" );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                quotationNumber = rset.getString ( "QUOTATION_NO" );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not get Automated Quotation No from database", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        if ( quotationNumber != null )
        {
            int seq = 0;
            try
            {
                seq = Integer.parseInt ( quotationNumber.substring( quotationNumber.length()-2 ) );
            } catch ( NumberFormatException e ) { seq = -1; }
            seq = seq + 1;
            if ( seq > 99 )
            {
                seq = 0;
            }
            sequence = (seq<10) ? "0"+seq : Integer.toString(seq);
        }
        newQuotationNumber = newQuotationNumber + sequence;
        /*if( quotationPrefix != null && quotationPrefix.trim().length() != 0 )
        {
            newQuotationNumber = quotationPrefix + "-" + newQuotationNumber;
        }*/
        return newQuotationNumber;
    }

    public static void splitQuotationLineItems ( DBTransaction dbTransaction, ArrayList lineItems, String userId ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        boolean success = false;
        int lineItemsSize = (lineItems == null) ? 0 : lineItems.size();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            cstmt = conn.prepareCall( "{ CALL SP_SPLIT_QUOTATION_LINE_ITEM ( ?, ?, ?, ? ) }" );
            for ( int i=0; i < lineItemsSize; i++ )
            {
                LineItem lineItem = (LineItem)lineItems.get ( i );
                if ( lineItem.isSplit() )
                {
                    cstmt.setInt ( 1, lineItem.getSplitFromLineItemId() );
                    cstmt.setInt ( 2, lineItem.getSplitQty() );
                    cstmt.setString ( 3, lineItem.getRequestDate() );
                    cstmt.setString ( 4, userId );

                    cstmt.addBatch();
                }
            }
            cstmt.executeBatch();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not Split Quotation Line Items", e );
        }
        finally
        {
            if ( dbTransaction == null && !success && conn != null )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }
    public static void cancelLineItems ( DBTransaction dbTransaction, ArrayList itemsToCancel, String userId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "UPDATE Quotation_Order_LineItem SET QLI_IS_CANCEL = 0, QLI_LAST_UPDATED_BY_ID = ?, QLI_LAST_UPDATED_BY_NAME = ?, TIMESTAMP = CURRENT_TIMESTAMP WHERE LINEITEM_ID = ?" );
            int listSize = (itemsToCancel == null) ? 0 : itemsToCancel.size();
            for ( int i = 0; i < listSize; i++ )
            {
                LineItem lineItem = (LineItem)itemsToCancel.get(i);

                pstmt.setString ( 1, userId );
                pstmt.setString ( 2, userId );
                pstmt.setInt  ( 3, lineItem.getLineItemId() );

                pstmt.addBatch();
            }
            pstmt.executeBatch();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not cancel quotation line item", e );
        }
        finally
        {
            if ( dbTransaction == null && !success && conn != null )
            {
                try { conn.rollback(); } catch ( SQLException ignore ){}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }
    public static ArrayList getQuoteTypes( DBTransaction dbTransaction ) throws ScalarException
    {
        ArrayList quoteTypes = new ArrayList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT QUOTE_TYPE_ID, QUOTE_TYPE, IS_DISABLED  FROM QUOTE_TYPES_TABLE ORDER BY QUOTE_TYPE" );
            rset = pstmt.executeQuery( );
            while ( rset.next() )
            {
                QuoteType quoteType = new QuoteType();
                quoteType.setQuoteTypeId( rset.getInt ( "QUOTE_TYPE_ID" )  );
                quoteType.setQuoteTypeName( rset.getString ( "QUOTE_TYPE" ) );
                quoteType.setDisabled( rset.getInt ( "IS_DISABLED" ) == 0 ? true : false );

                quoteTypes.add ( quoteType );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Quote Types", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return quoteTypes;
    }
    public static ArrayList getQuoteApplications( DBTransaction dbTransaction ) throws ScalarException
    {
        ArrayList applications = new ArrayList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT QUOTE_APPLICATION_ID, QUOTE_APPLICATION, IS_DISABLED FROM QUOTE_APPLICATIONS_TABLE ORDER BY QUOTE_APPLICATION" );
            rset = pstmt.executeQuery( );
            while ( rset.next() )
            {
                QuoteApplicationType quoteApplicationType = new QuoteApplicationType();
                quoteApplicationType.setAppTypeId( rset.getInt ( "QUOTE_APPLICATION_ID" ) );
                quoteApplicationType.setAppTypeName( rset.getString ( "QUOTE_APPLICATION" ) );
                quoteApplicationType.setDisabled( rset.getInt ( "IS_DISABLED" ) == 0 ? true : false );
                applications.add( quoteApplicationType );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Quote Applications", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return applications;
    }
    public static ArrayList getQuoteTypeLabelValueList( DBTransaction dbTransaction ) throws ScalarException
    {
        ArrayList quoteTypes = getQuoteTypes( dbTransaction );
        ArrayList labelValueList = new ArrayList();
        int size = (quoteTypes == null ) ? 0 : quoteTypes.size();
        for ( int i=0; i < size; i++ )
        {
            QuoteType quoteType = (QuoteType)quoteTypes.get(i);
            if ( !quoteType.isDisabled() )
            {
                labelValueList.add ( new LabelValueBean( quoteType.getQuoteTypeName(), quoteType.getQuoteTypeId()+"" ) );
            }
        }
        return labelValueList;
    }
    public static ArrayList getQuoteApplicationLabelValueList( DBTransaction dbTransaction ) throws ScalarException
    {
        ArrayList quoteApplications = getQuoteApplications( dbTransaction );
        ArrayList labelValueList = new ArrayList();
        int size = (quoteApplications == null ) ? 0 : quoteApplications.size();
        for ( int i=0; i < size; i++ )
        {
            QuoteApplicationType quoteApplicationType = (QuoteApplicationType)quoteApplications.get(i);
            if ( !quoteApplicationType.isDisabled() )
            {
                labelValueList.add ( new LabelValueBean( quoteApplicationType.getAppTypeName(), quoteApplicationType.getAppTypeId()+"" ) );
            }
        }
        return labelValueList;
    }
    public static boolean isQuoteTypeExists ( DBTransaction dbTransaction, String quoteType ) throws ScalarException
    {
        boolean isExists = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT COUNT(*) AS CNT FROM QUOTE_TYPES_TABLE WHERE QUOTE_TYPE = ?" );
            pstmt.setString ( 1, quoteType );
            rset = pstmt.executeQuery( );
            if ( rset.next() )
            {
                if ( rset.getInt ( "CNT" ) > 0 )
                {
                    isExists = true;
                }
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not load Quote Applications", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return isExists;
    }
    public static void saveQuoteType ( DBTransaction dbTransaction, String quoteType, int quoteTypeId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            if ( quoteTypeId > 0 )
            {
                pstmt = conn.prepareStatement( "UPDATE QUOTE_TYPES_TABLE SET QUOTE_TYPE = ? WHERE QUOTE_TYPE_ID = ?" );
                pstmt.setString ( 1, quoteType );
                pstmt.setInt ( 2, quoteTypeId );
            }
            else
            {
                pstmt = conn.prepareStatement ( "INSERT INTO QUOTE_TYPES_TABLE( QUOTE_TYPE, IS_DISABLED ) VALUES ( ?, 1 )" );
                pstmt.setString ( 1, quoteType );
            }
            pstmt.executeUpdate();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not save Quote Types", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }
    public static void updateQuoteTypeStatus ( DBTransaction dbTransaction, int quoteTypeId, boolean isDisabled ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "UPDATE QUOTE_TYPES_TABLE SET IS_DISABLED = ? WHERE QUOTE_TYPE_ID = ?" );
            pstmt.setInt ( 1, isDisabled ? 0 : 1 );
            pstmt.setInt ( 2, quoteTypeId );
            pstmt.executeUpdate();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not update Quote Type status", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }
    public static boolean isQuoteApplicationTypeExists ( DBTransaction dbTransaction, String quoteApplicationType ) throws ScalarException
    {
        boolean isExists = false;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT COUNT(*) AS CNT FROM QUOTE_APPLICATIONS_TABLE WHERE QUOTE_APPLICATION = ?" );
            pstmt.setString ( 1, quoteApplicationType );
            rset = pstmt.executeQuery( );
            if ( rset.next() )
            {
                if ( rset.getInt ( "CNT" ) > 0 )
                {
                    isExists = true;
                }
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not check Quote Application", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return isExists;
    }
    public static void saveQuoteApplicationType ( DBTransaction dbTransaction, String quoteApplicationType, int quoteApplicationTypeId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            if ( quoteApplicationTypeId > 0 )
            {
                pstmt = conn.prepareStatement( "UPDATE QUOTE_APPLICATIONS_TABLE SET QUOTE_APPLICATION = ? WHERE QUOTE_APPLICATION_ID = ?" );
                pstmt.setString ( 1, quoteApplicationType );
                pstmt.setInt ( 2, quoteApplicationTypeId );
            }
            else
            {
                pstmt = conn.prepareStatement ( "INSERT INTO QUOTE_APPLICATIONS_TABLE( QUOTE_APPLICATION, IS_DISABLED ) VALUES ( ?, 1 )" );
                pstmt.setString ( 1, quoteApplicationType );
            }
            pstmt.executeUpdate();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not save Quote Application Type", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }
    public static void updateQuoteApplicationTypeStatus ( DBTransaction dbTransaction, int quoteApplicationTypeId, boolean isDisabled ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement ( "UPDATE QUOTE_APPLICATIONS_TABLE SET IS_DISABLED = ? WHERE QUOTE_APPLICATION_ID = ?" );
            pstmt.setInt ( 1, isDisabled ? 0 : 1 );
            pstmt.setInt ( 2, quoteApplicationTypeId );
            pstmt.executeUpdate();
            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not update Quote Application Type status", e );
        }
        finally
        {
            if ( dbTransaction == null && conn != null && !success )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }

    public static int getAlarmIdByQuotationId( DBTransaction dbTransaction, int quoteId ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        int alarmId                 = -1;

        try
        {

            if( dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT Alarm_ID FROM Quotation_Table WHERE Quotation_Id = ?" );
            pstmt.setInt( 1, quoteId );

            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                alarmId = rset.getInt( "Alarm_ID" );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get alarmId for QuotationId from database", sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return alarmId;
    }

    public static ArrayList loadQuotationApprovers( DBTransaction dbTransaction ) throws ScalarException
    {
        return ( UserDAO.loadUsersWithModifyFunctionAccess( dbTransaction, Functionality.APPROVE_QUOTATION ) );
    }

    public static void notifyApprovedQuotationInfo( int quotationId ) throws ScalarException
    {

        Quotation quotation     = null;
        String from             = null;
        String to               = null;
        String cc               = null;
        String subject          = null;
        String message          = null;

        String poNoLabel                = null;
        String shippingMethod           = null;
        String shippingMethodLabel      = null;
        String endUserLabel             = null;
        String endCustomerLabel         = null;
        String representativeLabel      = null;
        String deliveryTermsLabel       = null;
        String heading                  = null;
        String method                   = null;
        String customerNotificatonEmail = null;

        String mfgPartNumberLabel       = null;
        String customerPartNumberLabel  = null;
        String labelNumberLabel         = null;
        StringBuffer htmlView           = new StringBuffer();

        try
        {
            try
            {
                poNoLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.salesOrder.PoNumber" );
                if ( poNoLabel == null )
                {
                    poNoLabel = Labels.DEFAULT_PO_NO;
                }
            }
            catch ( NullPointerException e )
            {
                poNoLabel = Labels.DEFAULT_PO_NO;
            }

            try
            {
                endCustomerLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.endCustomer" );
                if ( endCustomerLabel == null )
                {
                    endCustomerLabel = Labels.DEFAULT_END_CUSTOMER_LABEL;
                }
            }
            catch ( NullPointerException e )
            {
                endCustomerLabel = Labels.DEFAULT_END_CUSTOMER_LABEL;
            }
            try
            {
                endUserLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.endUser" );
                if ( endUserLabel == null )
                {
                    endUserLabel = Labels.DEFAULT_END_USER_LABEL;
                }
            }
            catch ( NullPointerException e )
            {
                endUserLabel    = Labels.DEFAULT_END_CUSTOMER_LABEL;
            }

            try
            {
                representativeLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.salesRep.Name" );
                if ( representativeLabel == null )
                {
                    representativeLabel = Labels.DEFAULT_REPRESENTATIVE;
                }
            }
            catch ( NullPointerException e )
            {
                representativeLabel = Labels.DEFAULT_REPRESENTATIVE;
            }
            try
            {
                shippingMethodLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.salesOrder.ShippingMethod" );
                if ( shippingMethodLabel == null )
                {
                    shippingMethodLabel = Labels.DEFAULT_SHIPPING_METHOD_LABEL;
                }
            }
            catch ( NullPointerException e )
            {
                shippingMethodLabel    = Labels.DEFAULT_SHIPPING_METHOD_LABEL;
            }
            try
            {
                deliveryTermsLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.salesOrder.DeliveryTerms" );
                if ( deliveryTermsLabel == null )
                {
                    deliveryTermsLabel = Labels.DEFAULT_DELIVERY_TERMS_LABEL;
                }
            }
            catch ( NullPointerException e )
            {
                deliveryTermsLabel    = Labels.DEFAULT_DELIVERY_TERMS_LABEL;
            }
            try
            {
                mfgPartNumberLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.mfgPartNumber" );
                if ( mfgPartNumberLabel == null )
                {
                    mfgPartNumberLabel = Labels.DEFAULT_MFG_PART_NUMBER_LABEL;
                }
            }
            catch ( NullPointerException e )
            {
                mfgPartNumberLabel  = Labels.DEFAULT_MFG_PART_NUMBER_LABEL;
            }
            try
            {
                customerPartNumberLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.customerPartNumber" );
                if ( customerPartNumberLabel == null )
                {
                    customerPartNumberLabel = Labels.DEFAULT_CUSTOMER_PART_NUMBER_LABEL;
                }
            }
            catch ( NullPointerException e )
            {
                customerPartNumberLabel  = Labels.DEFAULT_CUSTOMER_PART_NUMBER_LABEL;
            }
            try
            {
                labelNumberLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.labelNumber" );
                if ( labelNumberLabel == null )
                {
                    labelNumberLabel = Labels.DEFAULT_LABEL_NUMBER_LABEL;
                }
            }
            catch ( NullPointerException e )
            {
                labelNumberLabel  = Labels.DEFAULT_LABEL_NUMBER_LABEL;
            }

            String requestedPriceLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.requestedPrice" );
            if ( requestedPriceLabel == null )
            {
                requestedPriceLabel = Labels.DEFAULT_REQUESTED_PRICE_LABEL;
            }
            String custResalePriceLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.customerResalePrice" );
            if ( custResalePriceLabel == null )
            {
                custResalePriceLabel = Labels.DEFAULT_CUST_RESALE_PRICE_LABEL;
            }
            String priceLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.price" );
            if ( priceLabel == null )
            {
                priceLabel = Labels.DEFAULT_PRICE_LABEL;
            }
            String suggResalePriceLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.suggestedResalePrice" );
            if ( suggResalePriceLabel == null )
            {
                suggResalePriceLabel = Labels.DEFAULT_SUGG_RESALE_PRICE_LABEL;
            }

            if ( QuotationDAO.isQuotationApproved( null, quotationId ) )
            {
                from =  ConfigManager.getFromEmailId( Constants.FROM_EMAIL_SALES_GROUP_NAME );

                quotation = QuotationDAO.loadQuotation( quotationId + "", false );

                to = UserDAO.loadEmailByInternalUserId( null, quotation.getEnteredById() );

                if( to != null && to.trim().length() > 0 )
                {
                    to = quotation.getEnteredByName() + "<" + to + ">" ;
                }
                
                if ( ConfigManager.isSendApprovedQuotationNotificationMailToCustomer() )
                {
                    customerNotificatonEmail = StringUtils.concatStrings ( NewSalesOrderDAO.loadCustomerNotificationsEmails( quotation.getCustomerId(), "Quotation" ), Mailer.EMAIL_SEPARATOR );
                    if( customerNotificatonEmail != null && customerNotificatonEmail.trim().length() > 0 )
                    {
                        if( cc == null || cc.trim().length() == 0 )
                        {
                            cc = customerNotificatonEmail;
                        }
                        else
                        {
                            cc = cc + "," + customerNotificatonEmail;
                        }
                    }
                }

                if( ( to == null || to.trim().length() == 0 ) && ( cc != null && cc.trim().length() > 0 ) )
                {
                    to = cc;
                    cc = "";
                }

                method = quotation.getShippingMethodName();

                if( method != null && method.trim().length()!= 0 )
                {
                     shippingMethod = quotation.getShippingMethodName() + "#" + quotation.getShippingAccount();
                }
                else
                {
                    shippingMethod = "";
                }

                subject = "Quotation " + quotation.getQuotationNo() + " has been approved";
                //subject = "Quotation " + quotation.getQuotationNo() + " belongs to customer " + quotation.getCustomerName() + " has been approved";

                if( quotation.getFreightInstructions() == null )
                {
                    quotation.setFreightInstructions("");
                }

                message = "<HTML><BODY><P style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana'>This is to notify that the Quotation order <b>" + quotation.getQuotationNo() + "</b> has been approved. <BR><BR><TABLE WIDTH=677 BORDER=0 CELLPADDING=4 CELLSPACING=0 style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana'><TR BGCOLOR=\"#ccccff\"><TH COLSPAN=5 WIDTH=669 HEIGHT=20 VALIGN=CENTER><P ALIGN=LEFT> Quotation Order Information:</P></TH></TR>" +
                            "<TR BGCOLOR=\"#e6e6e6\"><TD WIDTH=136 VALIGN=TOP><P style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana'>Quotation No:</P></TD><TD WIDTH=167 VALIGN=BOTTOM SDVAL=\"10185\" SDNUM=\"1033;\"><P ALIGN=LEFT>" +
                            quotation.getQuotationNo() + "</P></TD><TD WIDTH=33 VALIGN=TOP><P><BR></P></TD><TD WIDTH=134 VALIGN=TOP><P>Customer Name:</P></TD><TD WIDTH=167 VALIGN=TOP><P>" +
                            quotation.getCustomerName() + "</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>Customer PO:</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getCustomerPO() + "</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT>Contact Person:</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getContactPerson() +	"</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>Phone No:</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            (quotation.getPhoneNumber() == null ? "" : quotation.getPhoneNumber() ) + "</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT>Cell No:</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            (quotation.getCellNumber() == null ? "" : quotation.getCellNumber()) +"</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>Fax :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            (quotation.getFaxNumber() == null ? "" : quotation.getFaxNumber() ) + "</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT>Email:</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            (quotation.getEmail() == null ? "" : quotation.getEmail() ) +"</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>Sales Person:</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getSalesPerson() + "</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT>"+representativeLabel+" :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getSalesRep() +	"</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>"+shippingMethodLabel+" :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            shippingMethod+"</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT></P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            "" +"</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>Pay Terms :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getShippingTerms() + "</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT>"+deliveryTermsLabel+" :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getDeliveryTerms() +	"</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>Regional Rep :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getRegionalRep() + "</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT>Quote Date:</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getQuotationDate() +"</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>"+endUserLabel+" :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getEndCustomer() + "</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT>"+endCustomerLabel+" :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getOdm() + "</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>Billing Address :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getBillingAddress() + "</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT>Shipping Address:</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getShippingAddress() +"</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>Freigh Instructions :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getFreightInstructions() + "</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT>Shipping Instructions:</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getShippingInstructions() +"</P></TD></TR><TR BGCOLOR=\"#e6e6e6\" VALIGN=TOP><TD WIDTH=136><P ALIGN=LEFT>Comments :</P></TD><TD WIDTH=167><P ALIGN=LEFT>" +
                            quotation.getComments() + 	"</P></TD><TD WIDTH=33><P ALIGN=LEFT><BR></P></TD><TD WIDTH=134><P ALIGN=LEFT></P></TD><TD WIDTH=167><P ALIGN=LEFT></TD></TR></TABLE></BODY></HTML>";


                if( to != null && to.trim().length() > 0 )
                {
                    Mailer.send( from,
                                 to,
                                 cc,
                                 null /*bcc*/,
                                 subject,
                                 message /*Message*/,
                                 null /*attachments*/,
                                 Mailer.CONTENT_TYPE_HTML,
                                 false /*Dont verify if the receipient is a ChipTrac user*/ );
                }
            }        
        }
        catch ( Exception e )
        {
            e.printStackTrace ();
            System.out.println ( "WARNING!! Could not notify " + to + " about the approved quotation : " + quotation.getQuotationNo () );
        }
    }

    public static boolean isQuotationApproved( DBTransaction dbTransaction, int quotationId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        boolean isApproved      = false;

        try
        {
            if( dbTransaction == null )
            {
                conn  = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT lineItem_Id from Quotation_Order_LineItem WHERE Quotation_ID = ? and IS_Approved = 1" );
            pstmt.setInt( 1, quotationId );
	        rs = pstmt.executeQuery();

            if( rs.next() == false )
	        {
                isApproved = true;
	        }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get pending line item IDs for quotation id: " + quotationId, sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }

        return isApproved;
   }


    public static String getQuotationStatus( String quotationNumber, String customerId, String manufacturingPartNo, String viewExpired, String userId, String customerPO, String endCustomer, String fromDate, String toDate )
    {
        String quotationStatus  = null;
        Orders orders           = null;
        ArrayList lineItems     = null;

        try
        {
            lineItems = loadQuotationsToChange( quotationNumber, customerId, manufacturingPartNo, viewExpired, userId, customerPO, endCustomer, fromDate, toDate);
        } catch (ScalarException e) { e.printStackTrace(); }


        if( lineItems != null )
        {
            for ( int i = 0; i < lineItems.size (); i++ )
            {
                orders = ( Orders ) lineItems.get ( i );

                if( orders.getIsTotallyApproved() == false && orders.getIsTotallyTransferred() )
                {
                    quotationStatus = SALESORDER_STATUS_HOLD;
                }
                else if( orders.getIsTotallyApproved() && orders.getIsTotallyTransferred() == false )
                {
                    quotationStatus = SALESORDER_STATUS_APPROVED;
                }
                else if( orders.getIsTotallyTransferred() )
                {
                    quotationStatus = "TRANSFERRED";
                }
                else
                {
                    quotationStatus = SALESORDER_STATUS_HOLD;
                }
                break;
            }
        }

        return quotationStatus;
    }

    public static String getQuotationCustomerName( DBTransaction dbTransaction, int quotationId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String customerName     = null;

        try
        {
            if( dbTransaction == null )
            {
                conn  = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT C_NAME FROM QUOTATION_TABLE QT, CUSTOMER C WHERE QT.CUSTOMER_ID = C.CUSTOMER_ID AND QT.QUOTATION_ID = ?" );
            pstmt.setInt( 1, quotationId );
	        rs = pstmt.executeQuery();

            if( rs.next() )
	        {
                customerName = rs.getString( "C_NAME" );
	        }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get Customer Name for quotation id: " + quotationId, sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }

        return customerName;

    }

    public static ArrayList  getPrevQuotationDetails ( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList prevQuotations = new ArrayList();
        PreviousQuotationDetails  prevQuotationDetails;
        boolean success = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            //pstmt = conn.prepareStatement ( "SELECT QUOTATION_NO, Q_BOOKING_DATE, CUSTOMER_ID  FROM Quotation_Table WHERE CUSTOMER_ID=? ORDER BY QUOTATION_NO DESC");
            pstmt=conn.prepareStatement("SELECT  QUOTATION_NO, QLI.QLI_BOOKING_DATE as BOOKING_DATE, Q_ENDCUSTOMER_ID, (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Q_ENDCUSTOMER_ID )AS END_CUSTOMER_NAME, Q_NEW_END_CUSTOMER, Q_ODM_ID, (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Q_ODM_ID)AS END_USER_NAME, Q_NEW_END_USER,  QLI.PRODUCT_ID, PART_NUMBER,  QLI_QTY, QLI.QLI_UNIT_COST AS UNIT_COST FROM QUOTATION_TABLE QT, PRODUCT P, QUOTATION_ORDER_LINEITEM QLI WHERE QT.QUOTATION_ID = QLI.QUOTATION_ID  AND QLI.PRODUCT_ID = P.PRODUCT_ID AND QT.CUSTOMER_ID = ? ORDER BY QT.QUOTATION_ID desc");
            pstmt.setInt ( 1, customerId );
            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                prevQuotationDetails = new PreviousQuotationDetails();
                prevQuotationDetails.setQuotationNo(rs.getString("QUOTATION_NO"));
                prevQuotationDetails.setQuotationDate(StringUtils.formatDateToString(rs.getDate("BOOKING_DATE")));
                prevQuotationDetails.setEndCustomerId(rs.getInt("Q_ENDCUSTOMER_ID"));
                prevQuotationDetails.setEndCustomerName(rs.getString("END_CUSTOMER_NAME"));
                prevQuotationDetails.setNewEndCustomerName(rs.getString("Q_NEW_END_CUSTOMER"));
                prevQuotationDetails.setOdmId(rs.getInt("Q_ODM_ID"));
                prevQuotationDetails.setEndUserName(rs.getString("END_USER_NAME"));
                prevQuotationDetails.setNewEndUserName(rs.getString("Q_NEW_END_USER"));
                prevQuotationDetails.setPartNumber(rs.getString("PART_NUMBER"));
                prevQuotationDetails.setQty(rs.getInt("QLI_QTY"));
                prevQuotationDetails.setUnitCost(rs.getFloat("UNIT_COST"));
                prevQuotationDetails.setOdmId(rs.getInt("Q_ODM_ID"));
                prevQuotations.add(prevQuotationDetails);
                //prevQuotations.add (new org.apache.struts.util.LabelValueBean(rs.getString("QUOTATION_NO"), StringUtils.formatDateToString(rs.getDate("Q_BOOKING_DATE"))));
            }

        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get prev Quotation Details lineItems Info from database", sqlex );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                        conn.commit ();
                    else
                        conn.rollback();
                }
            }
            catch ( SQLException ignore )
            {
            }
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return prevQuotations;
    }
    public static ArrayList  getPrevQuotationPriceDetails ( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList prevPriceQuotations = new ArrayList();
        PreviousQuotationDetails  prevQuotationDetails;
        boolean success = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            //pstmt = conn.prepareStatement ( "SELECT DISTINCT QLI.PRODUCT_ID, PART_NUMBER,  QLI.QLI_UNIT_COST AS UNIT_COST, QLI.QLI_BOOKING_DATE FROM QUOTATION_TABLE QT, PRODUCT P, QUOTATION_ORDER_LINEITEM QLI INNER JOIN(SELECT PRODUCT_ID,MAX(QLI_BOOKING_DATE) AS BD FROM QUOTATION_ORDER_LINEITEM GROUP BY PRODUCT_ID) AS XYZ ON XYZ.PRODUCT_ID = QLI.PRODUCT_ID AND XYZ.BD = QLI.QLI_BOOKING_DATE WHERE QT.QUOTATION_ID= QLI.QUOTATION_ID AND CUSTOMER_ID=? AND QLI.IS_APPROVED= 0 AND QLI.PRODUCT_ID = P.PRODUCT_ID ORDER BY QLI.PRODUCT_ID");
            //pstmt = conn.prepareStatement ( "SELECT DISTINCT QLI.PRODUCT_ID, PART_NUMBER,  QLI.QLI_UNIT_COST AS UNIT_COST, QLI.QLI_BOOKING_DATE FROM QUOTATION_TABLE QT, PRODUCT P, QUOTATION_ORDER_LINEITEM QLI INNER JOIN(SELECT PRODUCT_ID,MAX(QLI_BOOKING_DATE) AS BD FROM QUOTATION_ORDER_LINEITEM GROUP BY PRODUCT_ID) AS XYZ ON XYZ.PRODUCT_ID = QLI.PRODUCT_ID AND XYZ.BD = QLI.QLI_BOOKING_DATE WHERE QT.QUOTATION_ID= QLI.QUOTATION_ID AND CUSTOMER_ID=? AND QLI.IS_APPROVED= 0 AND QLI.PRODUCT_ID = P.PRODUCT_ID ORDER BY QLI.PRODUCT_ID");
            //pstmt = conn.prepareStatement ( "SELECT DISTINCT QLI.PRODUCT_ID, PART_NUMBER,  QLI.QLI_UNIT_COST AS UNIT_COST, QLI.QLI_BOOKING_DATE FROM QUOTATION_TABLE QT, PRODUCT P, QUOTATION_ORDER_LINEITEM QLI WHERE QT.QUOTATION_ID = QLI.QUOTATION_ID  AND QLI.PRODUCT_ID = P.PRODUCT_ID AND QLI.QLI_BOOKING_DATE = ( SELECT MAX(QLI_BOOKING_DATE) FROM QUOTATION_ORDER_LINEITEM Q1, QUOTATION_TABLE QT1, PRODUCT P1 WHERE Q1.PRODUCT_ID = QLI.PRODUCT_ID AND QT1.CUSTOMER_ID=? AND QT1.QUOTATION_ID= Q1.QUOTATION_ID )");
            pstmt=conn.prepareStatement("SELECT DISTINCT QUOTATION_NO, QLI.QLI_BOOKING_DATE as BOOKING_DATE, Q_ENDCUSTOMER_ID, (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Q_ENDCUSTOMER_ID )AS END_CUSTOMER_NAME, Q_NEW_END_CUSTOMER, Q_ODM_ID, (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Q_ODM_ID)AS END_USER_NAME,  Q_NEW_END_USER,  QLI.PRODUCT_ID, PART_NUMBER,  QLI_QTY, QLI.QLI_UNIT_COST AS UNIT_COST FROM QUOTATION_TABLE QT, PRODUCT P, QUOTATION_ORDER_LINEITEM QLI WHERE QT.QUOTATION_ID = QLI.QUOTATION_ID  AND QLI.PRODUCT_ID = P.PRODUCT_ID AND QLI.QLI_BOOKING_DATE = ( SELECT MAX(QLI_BOOKING_DATE) FROM QUOTATION_ORDER_LINEITEM Q1, QUOTATION_TABLE QT1, PRODUCT P1 WHERE Q1.PRODUCT_ID = QLI.PRODUCT_ID AND QT1.CUSTOMER_ID=? AND QT1.QUOTATION_ID= Q1.QUOTATION_ID )");
            pstmt.setInt ( 1, customerId );
            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                prevQuotationDetails = new PreviousQuotationDetails();
                prevQuotationDetails.setQuotationNo(rs.getString("QUOTATION_NO"));
                prevQuotationDetails.setQuotationDate(StringUtils.formatDateToString(rs.getDate("BOOKING_DATE")));
                prevQuotationDetails.setEndCustomerId(rs.getInt("Q_ENDCUSTOMER_ID"));
                prevQuotationDetails.setEndCustomerName(rs.getString("END_CUSTOMER_NAME"));
                prevQuotationDetails.setNewEndCustomerName(rs.getString("Q_NEW_END_CUSTOMER"));
                prevQuotationDetails.setOdmId(rs.getInt("Q_ODM_ID"));
                prevQuotationDetails.setEndUserName(rs.getString("END_USER_NAME"));
                prevQuotationDetails.setNewEndUserName(rs.getString("Q_NEW_END_USER"));
                prevQuotationDetails.setPartNumber(rs.getString("PART_NUMBER"));
                prevQuotationDetails.setQty(rs.getInt("QLI_QTY"));
                prevQuotationDetails.setUnitCost(rs.getFloat("UNIT_COST"));
                prevQuotationDetails.setOdmId(rs.getInt("Q_ODM_ID"));
                prevPriceQuotations.add(prevQuotationDetails);
                //prevQuotations.add (new org.apache.struts.util.LabelValueBean(rs.getString("QUOTATION_NO"), StringUtils.formatDateToString(rs.getDate("Q_BOOKING_DATE"))));
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Previous quotation leatest Prices of each Part Number Info from database", sqlex );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                        conn.commit ();
                    else
                        conn.rollback();
                }
            }
            catch ( SQLException ignore )
            {
            }
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return prevPriceQuotations;
    }

    public static void  loadQuotationQuestionnaireDetails ( DBTransaction dbTransaction, Quotation quotation ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList prevQuotations = new ArrayList();
        boolean success = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            String queryString = "SELECT CUSTOMER_ID, QUOTATION_ID , END_CUSTOMER_ID, ODM_ID, QUOTATON_TYPE, QUOTATION_NO, CURRENT_PRICE, CURRENT_PRICE_UNITS, CURRENT_PRICE_UNITS_TYPE, CURRENT_PRICE_OTHERS, TARGET_PRICE, TARGET_PRICE_UNITS, TARGET_PRICE_UNITS_TYPE, TARGET_PRICE_OTHERS, HISTORY_PRICE_INFO, IS_CONTRACT_PRICE, CONTRACT_PRICE_FILE_PATH, CONTRACT_PRICE_CHANGE_REASON, PREVIOUS_COMMITMENTS_QUANTITIES, PREVIOUS_COMMITMENTS_PRICING, PREVIOUS_COMMITMENTS_ADDTIONAL_INFO, IS_PREVIOUS_QUOTATION,PREVIOUS_QUOTATION_DETAILS,  PRICE_DISCOUNT_REASON_1, PRICE_DISCOUNT_REASON_2, PRICE_DISCOUNT_REASON_COMMNETS, IS_REBATE, REBATE_VALUE, REBATE_OPPERTUNITY, REBATE_GOAL, REBATE_TARGET_VALUE, REBATE_TARGET, REBATE_VERIFIED_BY, COMPETATIOR_1, COMPETATIOR_1_OTHERS, COMPETATIOR_1_PRICE, COMPETATIOR_1_PRICEUNIT, COMPETATIOR_1_COMMENTS, COMPETATIOR_2, COMPETATIOR_2_OTHERS, COMPETATIOR_2_PRICE, COMPETATIOR_2_PRICEUNIT, COMPETATIOR_2_COMMENTS, DISTRIBUTOR_MARGIN, MARGIN_DOLLARS, TOTAL_PREV_MARGINS, MARGIN_REASON, PREVIOUS_QUOTATION_NO, COMPETATIOR1PRICEUNITSTYPE, COMPETATIOR1PRICEUNITOTHERS,  COMPETATIOR2PRICEUNITSTYPE, COMPETATIOR2PRICEUNITOTHERS, REBATE_TARGET_GOAL_TIME_FRAME, REBATE_TARGET_GOAL_TIME_FRAME_OTHERS FROM QUOTATION_QUESTIONNAIRE_TABLE WHERE QUOTATION_ID=? ";
            pstmt = conn.prepareStatement ( queryString );
            pstmt.setInt ( 1, quotation.getQuotationId() );
            rs = pstmt.executeQuery ();
            if ( rs.next () )
            {
                quotation.setQuotationType(rs.getString("QUOTATON_TYPE") );
                quotation.setCurrentPrice(rs.getFloat("CURRENT_PRICE")  );
                quotation.setCurrentPriceUnits(rs.getInt("CURRENT_PRICE_UNITS") );
                quotation.setCurrentPriceUnitsType(rs.getString("CURRENT_PRICE_UNITS_TYPE") );
                quotation.setCurrentPriceOthers(rs.getString("CURRENT_PRICE_OTHERS") );
                quotation.setTargetPrice(rs.getFloat("TARGET_PRICE")  );
                quotation.setTargetPriceUnits(rs.getInt("TARGET_PRICE_UNITS") );
                quotation.setTargetPriceUnitsType(rs.getString("TARGET_PRICE_UNITS_TYPE") );
                quotation.setTargetPriceOthers(rs.getString("TARGET_PRICE_OTHERS"));
                quotation.setHistoryPricesInfo(rs.getString("HISTORY_PRICE_INFO"));// isCancel () ? 0 : 1 );
                quotation.setContractPriceFlag ((rs.getInt("IS_CONTRACT_PRICE")== 0 ? true: false ) );
                //quotation.setContractPriceFilePath (rs.getString("CONTRACT_PRICE_FILE_PATH"));
                quotation.setContractPriceChangedReason(rs.getString("CONTRACT_PRICE_CHANGE_REASON"));
                quotation.setPreviousCommitments(rs.getString("PREVIOUS_COMMITMENTS_QUANTITIES"));
                quotation.setPreviousCommitmentsPrice(rs.getString("PREVIOUS_COMMITMENTS_PRICING") );
                quotation.setCommitmentAddtionalInfo(rs.getString("PREVIOUS_COMMITMENTS_ADDTIONAL_INFO") );
                quotation.setPreviousQuotation(rs.getInt("IS_PREVIOUS_QUOTATION") == 0 ? true : false)  ;
                quotation.setPreviousQuotationDetails(rs.getString("PREVIOUS_QUOTATION_DETAILS") );
                quotation.setPriceDiscountReason(StringUtils.toStringArray(rs.getString("PRICE_DISCOUNT_REASON_1"),  Mailer.EMAIL_SEPARATOR ) );
                //quotation.setPriceDiscountReason(rs.getString("PRICE_DISCOUNT_REASON_2") );
                quotation.setPriceDiscountComments(rs.getString("PRICE_DISCOUNT_REASON_COMMNETS") );
                quotation.setRebate((rs.getInt("IS_REBATE")== 0 ? true : false ) );
                quotation.setRebateValue (rs.getFloat("REBATE_VALUE") );
                quotation.setRebateOpertunity(rs.getString("REBATE_OPPERTUNITY") );
                quotation.setRebateGoal(rs.getString("REBATE_GOAL") );
                quotation.setRebateTargetValue(rs.getFloat("REBATE_TARGET_VALUE") );
                quotation.setRebateTarget(rs.getString("REBATE_TARGET") );
                quotation.setRebateVerifiedBy(rs.getString("REBATE_VERIFIED_BY"));
                quotation.setCompetatior1(rs.getString("COMPETATIOR_1"));
                //quotation.setCompetatior1Others(rs.getString("COMPETATIOR_1_OTHERS") );
                quotation.setCompetatior1Price(rs.getFloat("COMPETATIOR_1_PRICE") );
                quotation.setCompetatior1PriceUnit(rs.getString("COMPETATIOR_1_PRICEUNIT") );
                quotation.setCompetatior1Comments(rs.getString("COMPETATIOR_1_COMMENTS"));
                quotation.setCompetatior2(rs.getString("COMPETATIOR_2"));
                //quotation.setCompetatior2Others(rs.getString("COMPETATIOR_2_OTHERS") );
                quotation.setCompetatior2Price(rs.getFloat("COMPETATIOR_2_PRICE") );
                quotation.setCompetatior2PriceUnit(rs.getString("COMPETATIOR_2_PRICEUNIT") );
                quotation.setCompetatior2Comments(rs.getString("COMPETATIOR_2_COMMENTS"));
                quotation.setDistributorMargin(rs.getFloat("DISTRIBUTOR_MARGIN") );
                quotation.setMarginDollars(rs.getFloat("MARGIN_DOLLARS") );
                quotation.setTotalPrevMargins(rs.getString("TOTAL_PREV_MARGINS") );
                quotation.setMarginReason(rs.getString("MARGIN_REASON") );
                quotation.setPrevQuotationNo(rs.getString("PREVIOUS_QUOTATION_NO"));
                quotation.setCompetatior1PriceUnitsType(rs.getString("COMPETATIOR1PRICEUNITSTYPE"));
                quotation.setCompetatior1PriceUnitOthers (rs.getString("COMPETATIOR1PRICEUNITOTHERS"));
                quotation.setCompetatior2PriceUnitsType(rs.getString("COMPETATIOR2PRICEUNITSTYPE"));
                quotation.setCompetatior2PriceUnitOthers(rs.getString("COMPETATIOR2PRICEUNITOTHERS"));
                quotation.setRebateTargetGoalTimeFrame(rs.getString("REBATE_TARGET_GOAL_TIME_FRAME"));
                quotation.setRebateTargetGoalTimeFrameOthers(rs.getString("REBATE_TARGET_GOAL_TIME_FRAME_OTHERS"));
            }
            success = true;
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get  quotation questionnaire Details Info from database", sqlex );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                        conn.commit ();
                    else
                        conn.rollback();
                }
            }
            catch ( SQLException ignore )
            {
            }
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
    }
     public static ArrayList  getPrevCommitmentsDetails ( DBTransaction dbTransaction, int customerId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList prevPriceQuotations = new ArrayList();
        PreviousQuotationDetails  prevQuotationDetails;
        boolean success = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt=conn.prepareStatement("SELECT SO.SALESORDER_ID,SO.QUOTATION_NO, SO.SALESORDER_NO,(SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID )AS END_CUSTOMER_NAME, SO_NEW_END_CUSTOMER, (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID)AS END_USER_NAME, SO_NEW_END_USER, P.PART_NUMBER, SUM(COOL.COOL_QTY) as COOL_QTY, SUM(ISNULL( XYZ.TOTAL, 0) )AS SHIPPED_QTY, ( SUM(COOL.COOL_QTY )- SUM(ISNULL( XYZ.TOTAL, 0 ) )) AS BACKLOG_QTY, SO.SO_BOOKING_DATE, XYZ.SHIP_DATE, MAX(COOL.COOL_SCHEDULE_DATE)AS COOL_SCHEDULE_DATE  FROM(((CUSTOMER_OPEN_ORDER_LINEITEM COOL LEFT OUTER JOIN (SELECT LINEITEM_ID, SUM( CSLIT_QTY_SHIPPED ) AS TOTAL, MAX(CST.CST_CONFIRMED_ON) AS SHIP_DATE FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT WHERE CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT_STATUS = 'SHIPPED' GROUP BY LINEITEM_ID ) XYZ ON COOL.LINEITEM_ID = XYZ.LINEITEM_ID INNER JOIN SALES_ORDER SO ON COOL.SALESORDER_ID = SO.SALESORDER_ID AND SO.SO_IS_SAMPLE = 1 )INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID )INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID)WHERE C.CUSTOMER_ID=? GROUP BY P.PRODUCT_ID, SO.SALESORDER_ID, SO.SALESORDER_NO, C.CUSTOMER_ID, C.C_NAME ,P.PART_NUMBER, SO.SO_Booking_Date, SO_NEW_END_CUSTOMER, SO_ENDCUSTOMER_ID, SO_ODM_ID, SO_NEW_END_USER, SO.QUOTATION_NO, XYZ.SHIP_DATE, COOL_SCHEDULE_DATE  ORDER BY SALESORDER_NO");
            //pstmt=conn.prepareStatement("SELECT QT.QUOTATION_NO, QT.Q_BOOKING_DATE, SO.SALESORDER_NO, P.PART_NUMBER, (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Q_ENDCUSTOMER_ID )AS END_CUSTOMER_NAME, Q_NEW_END_CUSTOMER, (SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = QT.Q_ODM_ID)AS END_USER_NAME, Q_NEW_END_USER, SUM(COOL.COOL_QTY) COOL_QTY, SUM(QLI_QTY) QLI_QTY FROM QUOTATION_TABLE QT, SALES_ORDER SO, QUOTATION_ORDER_LINEITEM QLI, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P WHERE QT.QUOTATION_ID=SO.QUOTATION_ID AND QLI.QUOTATION_ID=QT.QUOTATION_ID AND COOL.SALESORDER_ID= SO.SALESORDER_ID AND QLI.PRODUCT_ID=COOL.PRODUCT_ID  AND P.PRODUCT_ID = QLI.PRODUCT_ID AND QT.CUSTOMER_ID = ? GROUP BY P.PART_NUMBER, QT.QUOTATION_NO, SO.QUOTATION_NO, SO.SALESORDER_NO, QT.Q_ENDCUSTOMER_ID, QT.Q_ODM_ID, Q_NEW_END_CUSTOMER, Q_NEW_END_USER, QT.Q_BOOKING_DATE");
            pstmt.setInt ( 1, customerId );
            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                prevQuotationDetails = new PreviousQuotationDetails();
                prevQuotationDetails.setQuotationNo(rs.getString("QUOTATION_NO"));
                prevQuotationDetails.setSalesOrderNo(rs.getString("SALESORDER_NO"));
                //prevQuotationDetails.setQuotationDate(StringUtils.formatDateToString(rs.getDate("SO_BOOKING_DATE")));
                prevQuotationDetails.setEndCustomerName(rs.getString("END_CUSTOMER_NAME"));
                prevQuotationDetails.setNewEndCustomerName(rs.getString("END_CUSTOMER_NAME"));
                prevQuotationDetails.setEndUserName(rs.getString("END_USER_NAME"));
                prevQuotationDetails.setNewEndUserName(rs.getString("END_USER_NAME"));
                prevQuotationDetails.setPartNumber(rs.getString("PART_NUMBER"));
                prevQuotationDetails.setQty(rs.getInt("COOL_QTY"));
                prevQuotationDetails.setShipDate(StringUtils.formatDateToString(rs.getDate("SHIP_DATE")));
                prevQuotationDetails.setBacklogQty(rs.getInt("BACKLOG_QTY"));
                prevQuotationDetails.setScheduleDate(StringUtils.formatDateToString(rs.getDate("COOL_SCHEDULE_DATE")));
                prevPriceQuotations.add(prevQuotationDetails);
            }
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get Previous quotation leatest Prices of each Part Number Info from database", sqlex );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                        conn.commit ();
                    else
                        conn.rollback();
                }
            }
            catch ( SQLException ignore )
            {
            }
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return prevPriceQuotations;
    }
    public static ArrayList  getCompetitors ( DBTransaction dbTransaction) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList competitorsList = new ArrayList();
        String competatior= null;
        boolean success = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt=conn.prepareStatement("SELECT COMPETATIOR_NAME FROM COMPETITORS_TABLE");
            rs = pstmt.executeQuery ();
            while ( rs.next () )
            {
                competatior= rs.getString("COMPETATIOR_NAME");
                if ( competatior.trim()!="" && competatior!=  null )
                {
                    competitorsList.add (new org.apache.struts.util.LabelValueBean(competatior,competatior));
                }
            }
            competitorsList.add (new org.apache.struts.util.LabelValueBean("Other", "Other"));
            competitorsList.add (new org.apache.struts.util.LabelValueBean("N/A", "NA"));
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not COMPETITORs LIST Info from database", sqlex );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                        conn.commit ();
                    else
                        conn.rollback();
                }
            }
            catch ( SQLException ignore )
            {
            }
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return competitorsList;
    }
    public static void retrieveAttachedFileInformation( Quotation quotation ) throws ScalarException
    {
        String fileLocationContext  = ConfigManager.getQuestionnaireFilePathContext() + "/";
        String fileLocation         = ConfigManager.getQuestionnaireFilePath() + "/";
        try
        {
            String fileAttachmentOne        = fileLocation + quotation.getQuotationNo() + "/";
            String attachmentFileNameOne    = SalesOrderHelper.getFileAttachmentName( fileAttachmentOne );

            File file = null;

            file = new File ( fileAttachmentOne + attachmentFileNameOne );
            if( file == null || file.exists() == false || file.isFile() == false )
            {
                quotation.setContractPriceFileName( null );
                quotation.setContractPriceFileNamePath( null );
            }
            else
            {
                quotation.setContractPriceFileName( attachmentFileNameOne );
                quotation.setContractPriceFileNamePath( fileLocationContext + quotation.getQuotationNo() + "/" + quotation.getContractPriceFileName() );
            }
        }
        catch( Exception se ) { se.printStackTrace(); }

    }
     public static int getAlarmIdByQuotationNo( DBTransaction dbTransaction, String quotationNo ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        int alarmId                 = -1;

        try
        {

            if( dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT Alarm_ID FROM Quotation_Table WHERE Quotation_No = ?" );
            pstmt.setString( 1, quotationNo );

            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                alarmId = rset.getInt( "Alarm_ID" );
            }
        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get alarmId for QuotationId from database", sqle);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return alarmId;
    }

    public static int getQuotationIdByQuotationNo ( DBTransaction dbTransaction, String quotationNo ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList prevQuotations = new ArrayList();
        int quotationId = 0;
        boolean success = false;
        try
        {
            if (dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            String queryString = "SELECT QUOTATION_ID  FROM Quotation_Table WHERE QUOTATION_NO=? ";
            pstmt = conn.prepareStatement ( queryString );
            pstmt.setString ( 1, quotationNo  );
            rs = pstmt.executeQuery ();
            if ( rs.next () )
            {
                quotationId= rs.getInt("QUOTATION_ID");
            }
            success = true;
        }
        catch ( Exception sqlex )
        {
            sqlex.printStackTrace ();
            throw new ScalarException ( "Could not get  quotation Id Info from database", sqlex );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                        conn.commit ();
                    else
                        conn.rollback();
                }
            }
            catch ( SQLException ignore )
            {
            }
            if ( rs != null )
            {
                try
                {
                    rs.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close ();
                }
                catch ( SQLException excp )
                {
                }
            }
        }
        return quotationId;
    }


    public IPaginator getPreviousQuotationLatestPriceSearchDetails(String whereClause,int paginationSize, boolean ascorder, HttpServletRequest request)
    {
        IPaginator descPaginator=null;
        DBTransaction dbTransaction = null;
        try
        {
            //dbTransaction = DBTransaction.getInstance();
            HttpSession session = request.getSession();
            HashMap queryParams = new HashMap();
            String idQueryStr= "SELECT QUOTATION_NO FROM vw_Prev_Quotation_Price_Details_View";
            String retrQuery="SELECT QUOTATION_NO,BOOKING_DATE,isnull(END_CUSTOMER_NAME,isnull(Q_NEW_END_CUSTOMER,'')) as END_CUSTOMER_NAME,  isnull(END_USER_NAME, isnull(Q_NEW_END_USER,'')) as END_USER_NAME , PART_NUMBER,QLI_ACTUAL_QTY,UNIT_COST FROM  vw_Prev_Quotation_Price_Details_View WHERE QUOTATION_NO IN (ID_LIST)";
            StringBuffer idQuery = new StringBuffer(idQueryStr);
            idQuery.append(whereClause);
            return new IPaginatorImpl(idQuery.toString(),queryParams,retrQuery,queryParams,paginationSize,
                    "BOOKING_DATE",ascorder,dbTransaction);
        }
        catch(Exception e){
            e.printStackTrace();
        }

        return descPaginator;   
    }

    public IPaginator getPreviousQuotationsSearchDetails(String whereClause, int paginationSize, boolean ascorder, HttpServletRequest request)
    {
        IPaginator descPaginator=null;
        DBTransaction dbTransaction = null;
        try
        {
            //dbTransaction = DBTransaction.getInstance();
            HttpSession session = request.getSession();
            HashMap queryParams = new HashMap();
            String idQueryStr = "SELECT QUOTATION_NO FROM vw_Prev_Quotation_Details_View";
            //String retrQuery =  "SELECT QUOTATION_NO,BOOKING_DATE,isnull(END_CUSTOMER_NAME,isnull(Q_NEW_END_CUSTOMER,'')) as END_CUSTOMER_NAME,  isnull(END_USER_NAME, isnull(Q_NEW_END_USER,'')) as END_USER_NAME , PART_NUMBER,QLI_QTY,UNIT_COST FROM  vw_Prev_Quotation_Price_Details_View WHERE QUOTATION_NO IN (ID_LIST)";
            String retrQuery =  "SELECT  QUOTATION_NO, BOOKING_DATE, ISNULL(END_CUSTOMER_NAME,ISNULL(Q_NEW_END_CUSTOMER,'')) AS END_CUSTOMER_NAME,  ISNULL(END_USER_NAME, ISNULL(Q_NEW_END_USER,'')) AS END_USER_NAME , PART_NUMBER,  QLI_ACTUAL_QTY ,  UNIT_COST FROM VW_PREV_QUOTATION_DETAILS_VIEW WHERE QUOTATION_NO IN (ID_LIST)";
            StringBuffer idQuery = new StringBuffer(idQueryStr );
            idQuery.append( whereClause );
            
            return new IPaginatorImpl( idQuery.toString(),queryParams,retrQuery,queryParams,paginationSize,
                    "BOOKING_DATE",ascorder, dbTransaction);
        }
        catch(Exception e){
            System.out.println(" Exception while loading the quotation data ");
        }

        return descPaginator;
    }

    public IPaginator getPreviousCommitmentsDetailsSearch(String whereClause,int paginationSize, boolean ascorder, HttpServletRequest request)
        {
            IPaginator descPaginator=null;
            DBTransaction dbTransaction = null;
            try
            {
                //dbTransaction = DBTransaction.getInstance();
                HttpSession session = request.getSession();
                HashMap queryParams = new HashMap();
                String idQueryStr= "SELECT SALESORDER_NO FROM vw_previous_commitments_View";
                String retrQuery="SELECT ISNULL( QUOTATION_NO, '' ) AS QUOTATION_NO,SALESORDER_NO, isnull(END_CUSTOMER_NAME,isnull(SO_NEW_END_CUSTOMER,'')) as END_CUSTOMER_NAME,  isnull(END_USER_NAME, isnull(SO_NEW_END_USER,'')) as END_USER_NAME , PART_NUMBER, ISNULL(SHIP_DATE,'') AS SHIP_DATE, COOL_QTY, BACKLOG_QTY, ISNULL(SCHEDULE_DATE, '') AS SCHEDULE_DATE FROM  vw_previous_commitments_View WHERE SALESORDER_NO IN (ID_LIST)";
                StringBuffer idQuery = new StringBuffer(idQueryStr);
                idQuery.append(whereClause);
                return new IPaginatorImpl(idQuery.toString(),queryParams,retrQuery,queryParams,paginationSize,
                        "SALESORDER_NO",ascorder,dbTransaction);
            }
            catch(Exception e){
                e.printStackTrace();
            }

            return descPaginator;
        }
    public IPaginator getAllQuotationsDetailsSearch(String whereClause,int paginationSize, boolean ascorder, HttpServletRequest request)
        {
            IPaginator descPaginator=null;
            DBTransaction dbTransaction = null;
            try
            {
               HttpSession session = request.getSession();
               HashMap queryParams = new HashMap();
               String idQueryStr = "SELECT QUOTATION_NO FROM vw_Prev_Quotation_Details_View";
               //String retrQuery =  "SELECT QUOTATION_NO,BOOKING_DATE,isnull(END_CUSTOMER_NAME,isnull(Q_NEW_END_CUSTOMER,'')) as END_CUSTOMER_NAME,  isnull(END_USER_NAME, isnull(Q_NEW_END_USER,'')) as END_USER_NAME , PART_NUMBER,QLI_QTY,UNIT_COST FROM  vw_Prev_Quotation_Price_Details_View WHERE QUOTATION_NO IN (ID_LIST)";
               String retrQuery =  "SELECT  QUOTATION_NO, BOOKING_DATE, ISNULL(END_CUSTOMER_NAME,ISNULL(Q_NEW_END_CUSTOMER,'')) AS END_CUSTOMER_NAME,  ISNULL(END_USER_NAME, ISNULL(Q_NEW_END_USER,'')) AS END_USER_NAME , PART_NUMBER,  QLI_ACTUAL_QTY ,  UNIT_COST FROM VW_PREV_QUOTATION_DETAILS_VIEW WHERE QUOTATION_NO IN (ID_LIST)";
               StringBuffer idQuery = new StringBuffer(idQueryStr );
               idQuery.append( whereClause );
                return new IPaginatorImpl( idQuery.toString(),queryParams,retrQuery,queryParams,paginationSize,
                        "BOOKING_DATE",ascorder, dbTransaction);
            }
            catch(Exception e){
                e.printStackTrace();
            }

            return descPaginator;
        }
    public static String getQuotationSalesPersonName( DBTransaction dbTransaction, String quotationNo ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        String salesPersonName     = null;

        try
        {
            if( dbTransaction == null )
            {
                conn  = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT Q_SALES_PERSON FROM QUOTATION_TABLE QT WHERE QT.QUOTATION_NO = ?" );
            pstmt.setString( 1, quotationNo );
	        rs = pstmt.executeQuery();

            if( rs.next() )
	        {
                salesPersonName = rs.getString( "Q_SALES_PERSON" );
	        }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ( "Could not get salesPerson Name for quotation No: " + quotationNo, sqlex );
        }
        finally
        {
            if ( rs != null )
            {
                try
                { rs.close(); } catch ( SQLException excp ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException excp ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException excp ) {}
            }
        }

        return salesPersonName;

    }

    public static ArrayList<FieldProperty> getScreenDisplayFiledsList(DBTransaction dbTransaction) throws Exception {
        ScreenFieldProperties screenFieldProperties = ScreenFieldPropertiesDAO.getScreenFieldProperties(dbTransaction, "QUOTATION");
        Map fieldPropertiesMap = screenFieldProperties.getFieldProperties();
        ArrayList<FieldProperty> fieldProperties = new ArrayList<FieldProperty>();
        Collection temp = fieldPropertiesMap.values();
        Iterator itr = temp.iterator();
        while (itr.hasNext()) {
            FieldProperty fieldProperty = (FieldProperty) itr.next();
            if (fieldProperty.isDisplay()) {
                fieldProperties.add(fieldProperty);
            }
        }
        Collections.sort ( fieldProperties, new ScreenFieldPropertyComparator( ) );
        return fieldProperties;
    }
}